sp_executesql的用法

上次写的存储过程，需要从字符串的执行里面返回参数，用到了sp_executesql，如果只用exec是不行的。记录一下：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
CREATE PROCEDURE dbo.T_SendToHigher
@TableName varchar(255),
@ItemId varchar(4),
@ItemContent varchar(50),
@ItemFlag char(1)
/*
插入上传的数据到表中
*/
 AS
declare @Sql nvarchar(255)
declare @Flag nchar(1)

set @Sql=N'select @Flag=ItemFlag from '+@TableName+' where ItemId='+@ItemId+''
exec sp_executesql @sql,N'@Flag nchar(1) out',@Flag out

/*
如果上传数据在上级服务器上面没有时，进行插入操作
*/
if(@Flag = null)
begin
set @Sql="insert into "+@TableName+" values('"+@ItemId+"','"+@ItemContent+"','"+@ItemFlag+"')"
exec(@Sql)
end
/*
如果上传数据在上级服务器已经存在，但没有修改，执行更新操作
*/
if(@Flag = '0')
begin
set @Sql="update "+@TableName+" set ItemContent="+@ItemContent+",ItemFlag='0' where ItemId="+@ItemId
exec(@Sql)
end
GO

------------------------------------------------------------------------------------------------------------------------------

这个还有点游标的简单使用：
CREATE PROCEDURE Trans_SyncTableUploadState
/*
检查需要上传的表中，是否有需要上传的数据。有的话在publicinfo表中把该表对应的UploadState改为null
      publicinfo表中：
      UploadState 为null 时表示该表中有数据需要上传
      UploadState 为1时表示改表中没有数据需要上传
     
      publicinfo表中记录的需要上传的表中的UploadInfo字段：
      UploadState 为null时表示该条数据需要上传
      UploadInfoState 为1时表示该条数据不需要上传
*/
 AS
declare @TableName varchar(50)
declare @Sql nvarchar(200)
declare @IsNeedToTrans char(1)

declare TableName_Cursor cursor
for select SmallClassName from PublicInfo where BigClassNo=18
open TableName_Cursor
fetch next from TableName_Cursor into @TableName
while @@Fetch_Status = 0
begin
set @Sql=N'select @IsNeedToTrans=count(*) from '+@TableName+' where upload_state is null'
exec sp_executesql @Sql,N'@IsNeedToTrans char(1) out',@IsNeedToTrans out

if(@IsNeedToTrans != '0')
begin
update PublicInfo set upload_State=null where current of TableName_Cursor
end
else if(@IsNeedToTrans = '0')
begin
update PublicInfo set upload_State=1 where current of TableName_Cursor
end

fetch next from TableName_Cursor into @TableName
end
close TableName_Cursor
deallocate TableName_Cursor
GO