SQL总结之动态SQL（MSSQL）

通过后台语言编程的方式可以生成任意sql语句了，动态SQL到底还有什么用呢？

如果你仅仅只想在存储过程中能够让任意拼接的字符串（sql语句）能够执行的话，就必须用到动态sql了。当然，只要你愿意，这些逻辑完全可以使用编程语言来实现。

动态SQL使用较少，常用于存储过程中，给sql逻辑提供更大的灵活度。本人曾将此用于薪资模块，在存储过程中根据创建的薪资套动态地生成薪资表。

关于动态SQL的执行方式有两种，请看示例如下：

-- ============== 动态SQL ==================
--  1、exec 或execute不支持输出参数
exec('select * from sms_biz_UserInfo where Name = ''' + 'admin'  + '''')

-- 2、exec sp_executesql可以带输入参数和输出参数
-- 注意：SQL语句前必须带N
exec sp_executesql N'select * from sms_biz_UserInfo where Name = ''admin'''    -- 请注意字符串前一定要加N

-- (1)输入参数
     declare @name nvarchar(50) = 'admin'
     exec sp_executesql N'select * from sms_biz_UserInfo where Name = @name',N'@name nvarchar(50)',@name

-- (2)输出参数
     declare @count int
     exec sp_executesql N'select @count = count(*) from sms_biz_UserInfo',N'@count int output',@count output
     print @count

-- (3)输入、输出参数
 /*  注意：
     (1) 第一个参数是动态SQL
     (2) 第二个参数是动态SQL的“所有”（而不是单个）变量声明，
     (3) 第三个参数开始，必须按照第二个参数列表中声明顺序指定参数。
     (4) 输出参数，必须指定“output”,在第二个参数中声明，还是在第三个参数之后指定，都需要指定“output”字样！
     (5) 注意：动态sql中不支持表变量，但可以直接使用临时表(临时表名以#开关)
 */
     declare @count int
     declare @name nvarchar(50) = 'admin'
     exec sp_executesql N'select @count = count(*) from sms_biz_UserInfo where Name = @name',
        N'@count int output, @name nvarchar(50)',
        @count output,@name
     print @count

-- (4)动态SQL与临时表:创建临时表在动态SQL中可使用（只要在同一Session中）；
-- 但动态SQL中创建的临时表，不可在动态SQL外面使用
-- 正常
    select * into #t from ams_biz_AirTicketInfo
    exec('select * from #t')
-- 错误
    exec('select * into #t from ams_biz_AirTicketInfo')
    select * from #t