declare @sqls nvarchar(400)
create table #(a int ,b int)
set @sqls='insert into # select * from a'
exec(@sqls)
select * from # a b
5 1
9 2
11 3
22 4
33 5也许08可以了
create table #(a int ,b int)
set @sqls='insert into # select * from a'
exec(@sqls)
select * from # a b
5 1
9 2
11 3
22 4
33 5也许08可以了
@table sysname
as
declare @Count int
declare @sql nvarchar(1000)
set @sql='select @Count=count(*) from ' + @table
exec sp_executesql @sql,N'@Count int out',@Count out
return (@count)
--调用示例
declare @i int
exec @i=GetcountBytable 'sysobjects'
select @i
set @sqls='select @a=count(*) from syscolumns '
exec sp_executesql @sqls,N'@a int output',@num output
select @num ------
576
--将表作为参数的存储过程,求两表数据个数
create PROCEDURE pTotal
@Tab1 sysname,
@Tab2 sysname
as
declare @Count int,@sql nvarchar(1000)
set @sql='select @Count=count(*) from '+@Tab1
set @sql=@sql+char(10)+'select @Count=@Count+count(*) from '+@Tab2
exec sp_executesql @sql,N'@Count int out',@Count out
return (@Count)
go--调用示例
declare @i int
exec @i=pTotal 'sysobjects','sysfiles'
select @i
------------------ 列参数的问题---------------------------------
--charry0110
--方法一
declare @result varchar(20)
declare @col varchar(20)
set @col='id'
declare @id int
set @id=3
declare @sql nvarchar(1000)
set @sql = ' select '+@result+'='+@col +' from t where id =3'
exec sp_executesql @sql
--方法二
declare @col int
--你要的列
set @col=1
declare @id int
set @id = 3
declare @sql nvarchar(1000)
select @sql= ' select '+col_name(object_id( 't'),@col)+ ' from t where id=3'
exec sp_executesql @sql
------------完整列作为参数输入输出参数------------------------
--创建
CREATE proc Getcolreturn
@col varchar(20),
@C varchar(20),
@a money output
AS
declare @sql nvarchar(4000)
set @sql=N'select @a = ' + @col +' from t where id = '''+@C+''''
exec sp_executesql @sql,N'@a money out',@a out
go --调用
declare @result money
exec Getcolreturn 'InActive','8',@result out
select @resultgo
--删除测试
drop proc Getcolreturn
--------------------表参数问题-----------------------------
--charry0110
--一个表做参数的存储过程
alter procedure GetcountBytable
@table sysname
as
declare @Count int
declare @sql nvarchar(1000)
set @sql='select @Count=count(*) from ' + @table
exec sp_executesql @sql,N'@Count int out',@Count out
return (@count)
--将表作为参数的存储过程,求两表数据个数
create PROCEDURE pTotal
@Tab1 sysname,
@Tab2 sysname
as
declare @Count int,@sql nvarchar(1000)
set @sql='select @Count=count(*) from '+@Tab1
set @sql=@sql+char(10)+'select @Count=@Count+count(*) from '+@Tab2
exec sp_executesql @sql,N'@Count int out',@Count out
return (@Count)
go--调用示例
declare @i int
exec @i=pTotal 'sysobjects','sysfiles'
select @i
--存储过程根据日期动态创建表
create procedure CreateTableas
declare @table sysname
set @table=convert(varchar(6),getdate(),112)
declare @sql nvarchar(1000)
set @sql='create table "'+@table+ + '"(id int)'
exec sp_executesql @sql
go
--测试
exec CreateTable
select * from sysobjects where xtype='u' and name='200710'
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@a output
select @a