How to use dynamic sentence (exec/sp_executesql)

1. -- Dynamic statement syntax
2. /*************************************** **************************************** **************************************** *******************************
3. Dynamic statement Syntax: exec/sp_executesql syntax
5. Organized by: Roy)
7. Date: 2008.06.06
8. **************************************** **************************************** **************************************** ******************************/
9. Dynamic statement Syntax:
11. -- Method 1: Change the query table to dynamic
12. Select * From sysobjects
13. Exec ('select ID, name from sysobjects ')
14. Exec sp_executesql n 'select ID, name from sysobjects '-- an additional N is Unicode
16. -- Method 2: when the field name, table name, and database name are used as variables, dynamic SQL is used.
17. Declare @ fname varchar (20)
18. Set @ fname = 'id'
19. Exec ('select' + @ fname + 'from sysobjects where' + @ fname + '= 5 ')
22. Declare @ s varchar (1000)
23. Set @ s = n'select' + @ fname + 'from sysobjects where' + @ fname + '= 5'
24. Exec sp_executesql @ s -- an error is reported.
27. Declare @ s nvarchar (1000) -- change to nvarchar
28. Set @ s = n'select' + @ fname + 'from sysobjects where' + @ fname + '= 5'
29. Exec sp_executesql @ s -- successful
32. -- Method 3: input parameters
34. Declare @ I int, @ s nvarchar (1000)
35. Set @ I = 5
36. Exec ('select ID, name from sysobjects where id = '+ @ I)
38. Set @ s = 'select ID, name from sysobjects where id = @ I'
39. Exec sp_executesql @ s, n' @ I int ', @ I -- N is added to the input parameter here.
41. -- Method 4: Output Parameters
43. Declare @ I int, @ s nvarchar (1000)
44. Set @ s = 'select @ I = count (1) From sysobjects'
46. -- Exec
47. Exec ('Clare @ I int' + @ s + 'select @ I ') -- adds the entire statement to the string for execution.
49. -- Use sp_executesql
50. Exec sp_executesql @ s, n' @ I int output', @ I output -- N must be added to the output parameter here.
51. Select @ I
54. -- Method 5: Input and Output
56. -- Use sp_executesql
57. Declare @ I int, @ con int, @ s nvarchar (1000)
58. Set @ I = 5
59. Select @ s = 'select @ con = count (1) From sysobjects where ID> @ I'
60. Exec sp_executesql @ s, n' @ con int output, @ I int ', @ con output, @ I
61. Select @ con
63. -- Exec
64. Declare @ I int, @ s nvarchar (1000)
65. Set @ I = 5
66. Select @ s = 'Clare @ con int select @ con = count (1) From sysobjects where ID> '+ rtrim (@ I) + 'select @ con'
67. Exec (@ s)