Basic Syntax of dynamic SQL statements-difference between exec and exec sp_executesql

Basic Syntax of dynamic SQL statements
1: Common SQL statements 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 the field name, table name, database name and so on are used as variables, dynamic SQL 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, @ SQL nvarchar (4000)
Set @ SQL = 'select count (*) from tablename'
Exec (@ SQL)

-- How to put the exec execution result into a variable?

Declare @ num int, @ SQL nvarchar (4000)
Set @ SQL = 'select @ A = count (*) from tablename'
Exec sp_executesql @ SQL, n' @ A int output', @ num output
Select @ num

-----------------------

@ Maxnum int output -- maximum number

 

Declare @ idcount int -- used to store the variable assigned by the exec statement

Declare @ maxpagesql nvarchar (300) -- be sure to use nvarchaar

Set @ maxpagesql = 'select @ idcount = count (ID) from questioninfo where 1 = 1'

 

Exec sp_executesql @ maxpagesql, n' @ idcount int output', @ maxnum output
Select @ maxnum