Dynamic statement SQL statement notation

2. /************************************************************************************************************** ****************************************
4. Dynamic statement syntax: Exec/sp_executesql syntax
8. Finishing Man: China Wind (Roy)
12. Date: 2008.06.06
14. *************************************************************************************************************** ***************************************/
16. Dynamic statement Syntax:
20. --Method 1 query table changed to dynamic
22. SELECT * from sysobjects
24. EXEC (' select id,name from sysobjects ')
26. EXEC sp_executesql n' select id,name from sysobjects '-one more n for Unicode
30. --Method 2: Field name, table name, database name, etc. as variables, use dynamic SQL
32. DECLARE @FName varchar (20)
34. Set @FName =' ID '
36. EXEC (' select ' [email protected]+' from sysobjects where ' [email protected]+' =5 ')
42. DECLARE @s varchar (1000)
44. Set @s=n' SELECT ' [email protected]+' from sysobjects where ' [email protected]+' =5 '
46. EXEC sp_executesql @s--will error
52. declare @s nvarchar (1000)--Change to nvarchar
54. Set @s=n' SELECT ' [email protected]+' from sysobjects where ' [email protected]+' =5 '
56. EXEC sp_executesql @s--Success
62. --Method 3: Input parameters
66. DECLARE @i int,@s nvarchar (+)
68. Set @i=5
70. EXEC (' select Id,name from sysobjects where id= ' [email protected])
74. Set @s=' select Id,name from sysobjects where [email protected] '
76. EXEC sp_executesql @s,n' @i int ', @i--here input parameter to add N
80. --Method 4: Output parameters
84. DECLARE @i int,@s nvarchar (+)
86. Set @s=' select @i=count (1) from sysobjects '
90. --with Exec
92. EXEC (' Declare @i int ' [Email protected]+' Select @i ')--add the entire statement with a string to execute.
96. --With sp_executesql
98. EXEC sp_executesql @s,n' @i int output ', @i output--here output parameter to add N
100. Select @i
106. --Method 5: Input and output
110. --With sp_executesql
112. DECLARE @i int, @con int,@s nvarchar (+)
114. Set @i=5
116. Select @s=' Select @con =count (1) from sysobjects where id>@i '
118. EXEC sp_executesql @s,n' @con int output,@i int ', @con output, @i
120. Select @con
124. --with Exec
126. DECLARE @i int,@s nvarchar (+)
128. Set @i=5
130. Select @s=' declare @con int select @con =count (1) from sysobjects where id> ' +rtrim (@i) +' select @con '
132. EXEC (@s)

http://blog.csdn.net/roy_88/article/details/3020586

Dynamic statement SQL statement notation