Basic Syntax of dynamic SQL statements

1: Common SQL statements can be executed using Exec

Example: 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 the field name, table name, database name and so on are used as variables, dynamic SQL must be used

Error: 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.

Correct: 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

-- Note: The @ s parameter must be of the ntext, nchar, or nvarchar type, and change declare @ s varchar (1000) to declare @ s nvarchar (1000)

As follows:
Declare @ s nvarchar (1000) -- Note that this is changed to nvarchar (1000)

Set @ fname = 'filedname' -- set the field name
Set @ s = 'select' + @ fname + 'from tablename'

Exec (@ s) -- successful

Exec sp_executesql @ s -- this sentence is correct

3. input or output parameters

(1) input parameters:
Declare @ querystring nvarchar (1000) -- dynamic query statement variable (Note: It must be ntext or nchar nvarchar type, not varchar type)
Declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (Note: It must be of the ntext or nchar nvarchar type and cannot be of the varchar type)
Declare @ input_id int -- defines the parameter value of the dynamic statement to be passed in.

set @ querystring = 'select * From tablename where id = @ id' -- ID is the field name, @ ID is the parameter to be passed in
set @ paramstring = '@ ID int' -- set the defined string of the parameter in the dynamic statement
set @ input_id = 1 -- set the value of the input dynamic statement is 1
exec sp_executesql @ querystring, @ paramstring, @ ID = @ input_id

if multiple parameters exist:
declare @ querystring nvarchar (1000) -- dynamic query statement variable (note: it must be ntext or nchar nvarchar type, not varchar type)
declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (note: it must be ntext or nchar nvarchar type, and cannot be varchar type.)
declare @ input_id int -- defines the parameter value of the dynamic statement to be passed in, parameter 1
declare @ input_name varchar (20) -- defines the parameter value of the dynamic statement to be passed in. Parameter 2

set @ querystring = 'select * From tablename where id = @ ID and name = @ name' -- ID and name are field names, @ ID and @ name are the parameters to be passed in
set @ paramstring = '@ ID int, @ name varchar (20)' -- set the string defined by the parameters in the dynamic statement, use ", "separated
set @ input_id = 1 -- set the parameter value of the dynamic statement to be passed in to 1
set @ input_name = 'zhang san' -- set the parameter of the dynamic statement to be passed in the value is "zhangsan"
exec sp_executesql @ querystring, @ paramstring, @ ID = @ input_id, @ name = @ input_name -- pay attention to the order of parameters
(2) 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 @ querystring nvarchar (1000) -- dynamically queries language name variables (Note: ntext or nchar must be nvarchar, not varchar)
Declare @ paramstring nvarchar (200) -- set the parameter string in the dynamic statement (Note: It must be of the ntext or nchar nvarchar type and cannot be of the varchar type)
Declare @ output_result int -- the query result is assigned to @ output_result

Set @ querystring = 'select @ totalcount = count (*) from tablename' -- @ totalcount is the output result parameter.
Set @ paramstring = '@ totalcount int output' -- sets the defined string of parameters in a dynamic statement. Multiple parameters are separated by commas (,).
Exec sp_executesql @ querystring, @ paramstring, @ totalcount = @ output_result output
Select @ output_result

Of course, the input and output parameters can be used together. You can try them yourself.
In addition, if the result set of the dynamic statement query is to be output, I only want to use the temporary table method below. I wonder if you have any better method.
If object_id ('[tempdb]. [DBO]. # TMP') is not null -- determines whether a temporary table # TMP exists. If so, delete it.
Drop table # TMP
Select * into # TMP from tablename where 1 = 2 -- create a temporary table # TMP with the same structure as tablename

declare @ querystring nvarchar (1000) -- dynamically queries language name variables (Note: ntext or nchar must be nvarchar, not varchar)
set @ querystring = 'select * From tablename'
insert into # TMP (field1, field2 ,...) exec (@ querystirng)