Assigning values to variables with dynamic SQL statements

1: Normal SQL statements can be executed with exec
Eg:select * from TableName
Exec (' select * from TableName ')
sp_executesql n ' select * from TableName '--please note that the string must be added N before

2: Field name, table name, database name, etc. as variables, you must use dynamic SQL
eg
DECLARE @fname varchar(20)
Set @fname = ' [name] '
Select @fname from sysobjects--error
Exec (' select ' + @fname + ' from sysobjects ')--please note that a space is added to the edge of the single quote before and after the plus sign
EXEC sp_executesql N ' SELECT ' + @fname + ' from sysobjects '
Of course, changing the string to a variable can also
DECLARE @s varchar(1000)
Set @s = ' SELECT ' + @fname + ' from sysobjects '
Exec (@s)--Success
EXEC sp_executesql @s--This sentence will be an error

DECLARE @s Nvarchar(1000)--note here to Nvarchar(1000)
Set @s = ' SELECT ' + @fname + ' from sysobjects '
Exec (@s)--Success
EXEC sp_executesql @s--This sentence is correct,

3: Output parameters
eg
DECLARE @num,
@sqls
Set @sqls = ' SELECT count (*) from ' + @servername + '. A.dbo.b '
EXEC (@sqls)
Storing the results of exec execution in variable @num

DECLARE @num nvarchar(50),
@sqls nvarchar(1000)
Set @sqls = ' Select @a=count (*) from ' [email protected]+ '. A.dbo.b '
EXEC sp_executesql @sqls, N ' @a int output ', @num output
Select @num

--Data Type here if not nvarchar, an error will occur

Assigning values to variables with dynamic SQL statements