SQL SERVER 中构建执行动态SQL语句的方法
1:普通SQL语句可以用exec执行
Select*fromtableName
exec('select*fromtableName')
execsp_executesqlN'select*fromtableName'--请注意字符串前一定要加N
2:字段名，表名，数据库名之类作为变量时，必须用动态SQL
declare@fnamevarchar(20)
set@fname='FiledName'
--Select@fnamefromtableName--错误,不会提示错误，但结果为固定值FiledName,并非所要。
exec('select'+@fname+'fromtableName')--请注意加号前后的单引号的边上加空格
--当然将字符串改成变量的形式也可
declare@fnamevarchar(20)
set@fname='FiledName'--设置字段名
declare@svarchar(1000)
set@s='select'+@fname+'fromtableName'
exec(@s)--成功
--execsp_executesql@s--此句会报错
declare@sNvarchar(1000)--注意此处改为nvarchar(1000)(必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
set@s='select'+@fname+'fromtableName'
exec(@s)--成功
execsp_executesql@s--此句正确
3.输入或输出参数
--(1)输入参数:
declare@QueryStringnvarchar(1000)--动态查询语句变量(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@paramstringnvarchar(200)--设置动态语句中的参数的字符串(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@input_idint--定义需传入动态语句的参数的值
set@QueryString='select*fromtablenamewhereid=@id'--id为字段名，@id为要传入的参数
set@paramstring='@idint'--设置动态语句中参数的定义的字符串
set@input_id=1--设置需传入动态语句的参数的值为1
execsp_executesql@querystring,@paramstring,@id=@input_id
--若有多个参数:
declare@QueryStringnvarchar(1000)--动态查询语句变量(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@paramstringnvarchar(200)--设置动态语句中的参数的字符串(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@input_idint--定义需传入动态语句的参数的值,参数1
declare@input_namevarchar(20)--定义需传入动态语句的参数的值,参数2
set@QueryString='select*fromtablenamewhereid=@idandname=@name'--id与name为字段名，@id与@name为要传入的参数
set@paramstring='@idint,@namevarchar(20)'--设置动态语句中参数的定义的字符串,多个参数用","隔开
set@input_id=1--设置需传入动态语句的参数的值为1
set@input_name='张三'--设置需传入动态语句的参数的值为"张三"
execsp_executesql@querystring,@paramstring,@id=@input_id,@name=@input_name--请注意参数的顺序
--(2)输出参数
declare@numint,@sqlsnvarchar(4000)
set@sqls='selectcount(*)fromtableName'
exec(@sqls)
--如何将exec执行结果放入变量中？
declare@QueryStringnvarchar(1000)--动态查询语名变量(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@paramstringnvarchar(200)--设置动态语句中的参数的字符串(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
declare@output_resultint--查询结果赋给@output_result
set@QueryString='select@totalcount=count(*)fromtablename'--@totalcount为输出结果参数
set@paramstring='@totalcountintoutput'--设置动态语句中参数的定义的字符串,多个参数用","隔开
execsp_executesql@querystring,@paramstring,@totalcount=@output_resultoutput
select@output_result
--当然，输入与输出参数可以一起使用，大家可以自己去试一试。
--另外，动态语句查询的结果集要输出的话，我只想到以下用临时表的方法，不知各位有没有更好的方法.
IFobject_id('[tempdb].[dbo].#tmp')ISNOTNULL--判断临时表#tmp是否存在,存在则删除
droptable#tmp
select*into#tmpfromtablenamewhere1=2--创建临时表#tmp,其结构与tablename相同
declare@QueryStringnvarchar(1000)--动态查询语名变量(注：必须为ntext或nchar哐nvarchar类型，不能是varchar类型)
set@QueryString='select*fromtablename'
insertinto#tmp(field1,field2,)exec(@querystirng)
有些特殊原因，我们需要在SQL语句或者存储过程中动态创建SQL语句，然后在SQL语句或存储过程中动态来执行。
这里，微软提供了两个方法，一个是使用
Execute函数
执行方式为
Execute(@sql)来动态执行一个SQL语句，但是这里的SQL语句无法得到里面的返回结果，下面来介绍另一种方法
使用存储过程sp_ExecuteSql
使用该存储过程，则可将动态语句中的参数返回来。
比如
declare@sqlnvarchar(800),@ddvarchar(20) set@sql='set@mm=''测试字符串''' execsp_executesql@sql,N'@mmvarchar(20)output',@ddoutput select@dd
执行他就会将内部创建的SQL语句的某个变量的值返回到外部调用者。
主要来源于工作中的一个偶然需要：
createprocproc_InToServer@收费站点编号varchar(4),@车道号tinyint,@进入时间varchar(23),@UIDchar(16),
@车牌varchar(12),@车型char(1),@识别车牌号varchar(12),@识别车型char(1),@收费金额money,@交易状态char(1),
@有图像bit,@离开时间varchar(23),@速度float,@HasInsertintoutput
as
begin
declare@inTimedatetime,@TableNamevarchar(255),@leaveTimedatetime,@HasTablebit,@Sqlnvarchar(4000)
select@intime=Convert(datetime,@进入时间),@leaveTime=Convert(datetime,@离开时间)
set@TableName='ETC03_01_OBE原始过车记录表_'+dbo.formatDatetime(@intime,'YYYYMMDD')
select@HasTable=(CasewhenCount(*)>0then1else0end)fromsysobjectswhereid=Object_id(@TableName)andObjectProperty(id,'IsUserTable')=1
if@HasTable=0
begin
set@Sql='CREATETABLE[dbo].['+@TableName+'](
[收费站点编号][char](4)COLLATEChinese_PRC_CI_ASNOTNULL,
[车道号][tinyint]NOTNULL,
[进入时间][datetime]NOTNULL,
[UID][char](16)COLLATEChinese_PRC_CI_ASNOTNULL,
[车牌][varchar](12)COLLATEChinese_PRC_CI_ASNULL,
[车型][char](1)COLLATEChinese_PRC_CI_ASNULL,
[识别车牌号][varchar](12)COLLATEChinese_PRC_CI_ASNULL,
[识别车型][char](1)COLLATEChinese_PRC_CI_ASNULL,
[收费金额][money]NULL,
[交易状态][char](1)COLLATEChinese_PRC_CI_ASNULL,
[有图像][bit]NOTNULL,
[离开时间][datetime]NULL,
[速度][float]NULL,
Constraint'+'PK_'+@TableName+'primarykey(收费站点编号,车道号,进入时间,UID)
)ON[PRIMARY]'
Execute(@Sql)
end
set@sql='select@Cnt=count(*)from'+@TableName+'where收费站点编号='''+@收费站点编号+'''and车道号='+cast(@车道号asvarchar(4))+'and进入时间='''+@进入时间+'''andUID='''+@UID+''''
set@sql=@sql+'if@Cnt=0'
set@sql=@sql+'insert'+@TableName+'values('''+@收费站点编号+''','+cast(@车道号asvarchar(4))+','''+@进入时间+''','''+@Uid+''','''+@车牌+
''','''+@车型+''','''+@识别车牌号+''','''+@识别车型+''','+Cast(@收费金额asvarchar(8))+','''+@交易状态+''','+cast(@有图像asvarchar(1))+
','''+@离开时间+''','+Cast(@速度asvarchar(8))+')'
--Execute(@sql)
execsp_executesql@sql,N'@Cntintoutput',@HasInsertoutput
end
这样大家基本上就有些了解了。