參照用法  
 --動態(tài)語句語法 
 /****************************************************************************************************************************************************** 
 動態(tài)語句語法:exec\sp_executesql語法 
  
 整理人：中國風(fēng)(Roy) 
  
 日期:2008.06.06 
 ******************************************************************************************************************************************************/ 
 動態(tài)語句語法: 
  
 --方法1查詢表改為動態(tài) 
 select * from sysobjects 
 exec('select ID,Name from sysobjects') 
 exec sp_executesql N'select ID,Name from sysobjects'--多了一個(gè)N為unicode 
  
 --方法2:字段名，表名，數(shù)據(jù)庫名之類作為變量時(shí),用動態(tài)SQL  
 declare @FName varchar(20) 
 set @FName='ID' 
 exec('select '+@FName+' from sysobjects where '+@FName+'=5' ) 
  
  
 declare @s varchar(1000) 
 set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5' 
 exec sp_executesql @s--會報(bào)錯(cuò) 
  
  
 declare @s nvarchar(1000)--改為nvarchar 
 set @s=N'select '+@FName+' from sysobjects where '+@FName+'=5' 
 exec sp_executesql @s--成功 
  
  
 --方法3:輸入?yún)?shù) 
  
 declare @i int,@s nvarchar(1000) 
 set @i=5 
 exec('select ID,Name from sysobjects where ID='+@i) 
  
 set @s='select ID,Name from sysobjects where ID=@i' 
 exec sp_executesql @s,N'@i int',@i--此處輸入?yún)?shù)要加上N 
  
 --方法4:輸出參數(shù) 
  
 declare @i int,@s nvarchar(1000) 
 set @s='select @i=count(1) from sysobjects' 
  
 --用exec 
 exec('declare @i int '+@s+' select @i')--把整個(gè)語句用字符串加起來執(zhí)行 
  
 --用sp_executesql 
 exec sp_executesql @s,N'@i int output',@i output--此處輸出參數(shù)要加上N 
 select @i 
  
  
 --方法5:輸入輸出 
  
 --用sp_executesql 
 declare @i int,@con int,@s nvarchar(1000) 
 set @i=5 
 select @s='select @con=count(1) from sysobjects where ID>@i' 
 exec sp_executesql @s,N'@con int output,@i int',@con output ,@i  
 select @con 
  
 --用exec 
 declare @i int,@s nvarchar(1000) 
 set @i=5 
 select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con' 
 exec(@s)

eg:

DECLARE @sql nvarchar(1000),@parm varchar(20)
DECLARE @out_1 varchar(200),@out_2 varchar(200)
SET @parm = cast(92 AS varchar(5)) --,@out_2 = companyname
SET @sql = 'select @out_1 = city ,@out_2 = companyname from customers where Customerid ='''+ @parm+ ''''
SELECT @sql
EXEC sp_executesql @sql,N'@out_1 varchar(200) output,@out_2 varchar(200) output',@out_1 output,@out_2 output
SELECT @out_1,@out_2

DECLARE @sql nvarchar(1000),@parm varchar(20) 
DECLARE @out_1 varchar(200),@out_2 varchar(200) 
SET @parm = cast(92 AS varchar(5)) --,@out_2 = companyname 
SET @sql = 'select @out_1 = city ,@out_2 = companyname from customers where Customerid ='''+ @parm+ '''' 
SELECT @sql = @sql + 'SELECT @out_1,@out_2'
EXEC sp_executesql @sql,N'@out_1 varchar(200) output, @out_2 varchar(200) output',@out_1 output,@out_2 output