Basic Syntax of dynamic SQL statements in MSSQL

1. Common SQL Statement can be executed using Exec
Eg: Select * From tablename
Exec ('select * From tablename ')
Exec sp_executesql n 'select * From tablename' -- note that N must be added before the string.
2. When field names, table names, database names, and so on are used as variables, dynamic SQL statements must be used.
Eg:
Declare @ fname varchar (20)
Set @ fname = 'filedname'
Select @ fname from tablename -- error. No error is prompted, but the result is a fixed value of filedname, which is not required.
Exec ('select' + @ fname + 'from tablename') -- Note that spaces are placed on the side of the single quotation mark before and after the plus sign.
Of course, you can also change the string to a variable.
Declare @ fname varchar (20)
Set @ fname = 'filedname' -- set the field name
Declare @ s varchar (1000)
Set @ s = 'select' + @ fname + 'from tablename'
Exec (@ s) -- successful
Exec sp_executesql @ s -- this sentence will report an error
Declare @ s nvarchar (1000) -- Note that this is changed to nvarchar (1000)
Set @ s = 'select' + @ fname + 'from tablename'
Exec (@ s) -- successful
Exec sp_executesql @ s -- this sentence is correct
3. Output Parameters
Declare @ num int,
@ Sqls nvarchar (4000)
Set @ sqls = 'select count (*) from tablename'
Exec (@ sqls)
-- How to put the exec execution result into a variable?
Declare @ num int,
@ Sqls nvarchar (4000)
Set @ sqls = 'select @ A = count (*) from tablename'
Exec sp_executesql @ sqls, n' @ A int output', @ num output
Select @ num
In addition, if you want to use single quotes ''in SQL statement strings, you can use ''''