CREATE PROCEDURE myproc (@TableName [varchar](20))AS Execute('select * from '+@TableName) 要用Execute,否则select * from @TableName 将被解释为SQL命令加字符串,显然是不对的,所以要用字符串相加为一个合法的SQL语句,用Execute执行
create procedure myproc @tn varchar (20) as exec('select * from '+@tn) go
這個存储过程是用來把@Table中所有字段為NULL的改為“” 請參考!CREATE PROCEDURE UpdateNull @Table varchar(50) AS declare @id int declare @FieldName varchar(50) select @id=[id] from sysobjects where [name]=@Table select [name] into #cName from syscolumns where [id]=@id
while exists(select * from #cName) begin select top 1 @FieldName=[name] from #cName exec('update ' +@Table+' set '+@FieldName+'=space(0) where '+@FieldName+' is null') delete #cName where [name]=@FieldName end -------------------------------- drop table #cName GO
在Sql Server中,用参数作表名,该参数必须是表名类型;可以用动态sql实现:decalre @ssql char(100) select @ssql='select * from '+@tn exec(@ssql)
(@TableName [varchar](20))AS
Execute('select *
from '+@TableName)
要用Execute,否则select * from @TableName 将被解释为SQL命令加字符串,显然是不对的,所以要用字符串相加为一个合法的SQL语句,用Execute执行
create procedure myproc @tn varchar (20)
as
exec('select * from '+@tn)
go
請參考!CREATE PROCEDURE UpdateNull
@Table varchar(50)
AS declare @id int
declare @FieldName varchar(50)
select @id=[id] from sysobjects where [name]=@Table
select [name] into #cName from syscolumns where [id]=@id
while exists(select * from #cName)
begin
select top 1 @FieldName=[name] from #cName
exec('update ' +@Table+' set '+@FieldName+'=space(0) where '+@FieldName+' is null')
delete #cName where [name]=@FieldName
end
--------------------------------
drop table #cName
GO
select @ssql='select * from '+@tn
exec(@ssql)