查看完整版本: SQL code动态sql语句基本语法

SQL code动态sql语句基本语法

SQL code [color=#000000]动态sql语句基本语法 [/color][b]1[/b][color=#000000] :普通SQL语句可以用Exec执行 eg:   [/color][color=#0000ff]Select[/color]
[color=#808080]*[/color]
[color=#0000ff]from[/color][color=#000000] tableName          [/color][color=#0000ff]Exec[/color][color=#000000]([/color][color=#ff0000]'[/color][color=#ff0000]select * from tableName[/color][color=#ff0000]'[/color][color=#000000])          [/color][color=#0000ff]Exec[/color][color=#000000] sp_executesql N[/color][color=#ff0000]'[/color][color=#ff0000]select * from tableName[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080] 请注意字符串前一定要加N [/color]

[b]2[/b][color=#000000]:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg:   [/color][color=#0000ff]declare[/color]
[color=#008000]@fname[/color]
[color=#0000ff]varchar[/color][color=#000000]([/color][b]20[/b][color=#000000]) [/color][color=#0000ff]set[/color]
[color=#008000]@fname[/color]
[color=#808080]=[/color]
[color=#ff0000]'[/color][color=#ff0000]FiledName[/color][color=#ff0000]'[/color]
[color=#0000ff]Select[/color]
[color=#008000]@fname[/color]
[color=#0000ff]from[/color][color=#000000] tableName              [/color][color=#008080]--[/color][color=#008080] 错误,不会提示错误，但结果为固定值FiledName,并非所要。 [/color]
[color=#0000ff]Exec[/color][color=#000000]([/color][color=#ff0000]'[/color][color=#ff0000]select [/color][color=#ff0000]'[/color]
[color=#808080]+[/color]
[color=#008000]@fname[/color]
[color=#808080]+[/color]
[color=#ff0000]'[/color][color=#ff0000] from tableName[/color][color=#ff0000]'[/color][color=#000000])     [/color][color=#008080]--[/color][color=#008080] 请注意 加号前后的 单引号的边上加空格 [/color]
[color=#000000]当然将字符串改成变量的形式也可 [/color][color=#0000ff]declare[/color]
[color=#008000]@fname[/color]
[color=#0000ff]varchar[/color][color=#000000]([/color][b]20[/b][color=#000000]) [/color][color=#0000ff]set[/color]
[color=#008000]@fname[/color]
[color=#808080]=[/color]
[color=#ff0000]'[/color][color=#ff0000]FiledName[/color][color=#ff0000]'[/color]
[color=#008080]--[/color][color=#008080]设置字段名 [/color]

[color=#0000ff]declare[/color]
[color=#008000]@s[/color]
[color=#0000ff]varchar[/color][color=#000000]([/color][b]1000[/b][color=#000000]) [/color][color=#0000ff]set[/color]
[color=#008000]@s[/color]
[color=#808080]=[/color]
[color=#ff0000]'[/color][color=#ff0000]select [/color][color=#ff0000]'[/color]
[color=#808080]+[/color]
[color=#008000]@fname[/color]
[color=#808080]+[/color]
[color=#ff0000]'[/color][color=#ff0000] from tableName[/color][color=#ff0000]'[/color]
[color=#0000ff]Exec[/color][color=#000000]([/color][color=#008000]@s[/color][color=#000000])                [/color][color=#008080]--[/color][color=#008080] 成功 [/color]
[color=#0000ff]exec[/color][color=#000000] sp_executesql [/color][color=#008000]@s[/color]
[color=#008080]--[/color][color=#008080] 此句会报错 [/color]

[color=#0000ff]declare[/color]
[color=#008000]@s[/color]
[color=#0000ff]Nvarchar[/color][color=#000000]([/color][b]1000[/b][color=#000000])  [/color][color=#008080]--[/color][color=#008080] 注意此处改为nvarchar(1000) [/color]
[color=#0000ff]set[/color]
[color=#008000]@s[/color]
[color=#808080]=[/color]
[color=#ff0000]'[/color][color=#ff0000]select [/color][color=#ff0000]'[/color]
[color=#808080]+[/color]
[color=#008000]@fname[/color]
[color=#808080]+[/color]
[color=#ff0000]'[/color][color=#ff0000] from tableName[/color][color=#ff0000]'[/color]
[color=#0000ff]Exec[/color][color=#000000]([/color][color=#008000]@s[/color][color=#000000])                [/color][color=#008080]--[/color][color=#008080] 成功     [/color]
[color=#0000ff]exec[/color][color=#000000] sp_executesql [/color][color=#008000]@s[/color]
[color=#008080]--[/color][color=#008080] 此句正确 [/color]

[b]3[/b][color=#000000]. 输出参数 [/color][color=#0000ff]declare[/color]
[color=#008000]@num[/color]
[color=#0000ff]int[/color][color=#000000],         [/color][color=#008000]@sqls[/color]
[color=#0000ff]nvarchar[/color][color=#000000]([/color][b]4000[/b][color=#000000]) [/color][color=#0000ff]set[/color]
[color=#008000]@sqls[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]select count(*) from tableName[/color][color=#ff0000]'[/color]
[color=#0000ff]exec[/color][color=#000000]([/color][color=#008000]@sqls[/color][color=#000000]) [/color][color=#008080]--[/color][color=#008080]如何将exec执行结果放入变量中？ [/color]

[color=#0000ff]declare[/color]
[color=#008000]@num[/color]
[color=#0000ff]int[/color][color=#000000],                [/color][color=#008000]@sqls[/color]
[color=#0000ff]nvarchar[/color][color=#000000]([/color][b]4000[/b][color=#000000]) [/color][color=#0000ff]set[/color]
[color=#008000]@sqls[/color][color=#808080]=[/color][color=#ff0000]'[/color][color=#ff0000]select @a=count(*) from tableName [/color][color=#ff0000]'[/color]
[color=#0000ff]exec[/color][color=#000000] sp_executesql [/color][color=#008000]@sqls[/color][color=#000000],N[/color][color=#ff0000]'[/color][color=#ff0000]@a int output[/color][color=#ff0000]'[/color][color=#000000],[/color][color=#008000]@num[/color][color=#000000] output [/color][color=#0000ff]select[/color]
[color=#008000]@num[/color]

详细!学习中!