Dynamic SQL in T-SQL

First of all, the dynamic SQL is not part of the stored procedure, but constitutes its own scope. So, it is a independent thread. Your task indeed is to pass the bind variable and/or parameter into that thread and retrieve the result back to your stored procedure.

There are two ways to invoke dynamic SQL, EXEC sp_executesql and EXEC(). EXEC() is simple and useful when SQL Server version is older than 2005. Since EXEC only permits string literals and string variables to be concatenated and not arbitrary expressions, and since you cannot use parameters, you cannot as easily get values out from EXEC(). Let us make it easier: Never use EXEC() in stored procedure. Let DBA use it for their tasks.

In EXEC sp_executesql, the first built-in parameter @stmt is the SQL query statement in string. It is best to declare it as NVARCHAR datatype. So does for the second built-in parameter @params.

If your own parameter is of column name and table name, please use this way: quotename(@column_or_table_name). Else, it won't work. By doing this, you indeed is to embed column name and table name as bind variable into the SQL script. It thus no longer the parameters of built-in stored procedure sp_executesql. So, please declare it outside the sp_executesql, and do not include it in the built-in parameter @params.

To get the query result out from the sp_executesql, in your SQL query statement, the following SELECT style is necessary:

SET @sqlStatement = 'SELECT @avgResult = AVG(' + quotename(@Column_Name) + ' ) FROM Tab_Name'

In addition, your own parameter for sp_executesql needs to be declared as OUTPUT.

After first two built-in parameters for sp_executesql, you will need to assign values to your own parameters for sp_executesql.

Okey, here is an example:

DECLARE @result INT
DECLARE @EachColumn VARCHAR(30)
DECLARE curs CURSOR FOR
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Temp_Transform_Result'
OPEN curs
FETCH NEXT FROM curs INTO @EachColumn
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @exeString NVARCHAR(2000)
SET @exeString = ' SELECT @result = CONVERT(INT, AVG(' + quotename(@EachColumn) + '))
FROM Temp_Transform_Result
'
EXEC sp_executesql @exeString
, N'@result INT OUTPUT'
, @result OUTPUT
SET @exeString = ' INSERT INTO Temp_Transform_Input
VALUES ( ''IQM''
, @EachColumn
, @result
)
'
EXEC sp_executesql @exeString
, N'@EachColumn VARCHAR(30)
,@result INT
'
, @EachColumn = @EachColumn
, @result = @result
FETCH NEXT FROM curs INTO @EachColumn
END
CLOSE curs
DEALLOCATE curs

In first sp_executesql, @EachColumn is an embed bind variable rather than parameters. On contrast, in second sp_executesql, it is parameter. You may note, same parameter names used for sp_executesql and outsider stored procedure. So you see

, @EachColumn = @EachColumn
, @result = @result

The left hand side is parameter for sp_executesql, and the right hand side is the variable for stored procedure.


http://www.sommarskog.se/dynamic_sql.html