Using variables within a dynamic sql script

Question

I'm currently editing a procedure, let's call it A, that does some manipulations of a temporary table created in procedure B (so procedure B calls A). Procedure B contains one argument, which is the name of the said temporary table.

That being said, we need to retrieve all the columns (in a CSV) of the temporary table into a variable. Since the name is not the same everytime, I need to use dynamic SQL.

Here is what I have:

-- Input parameters: @sTempTableName VARCHAR(MAX) --> Name of the temporary table to alter

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS VARCHAR(MAX)

            
SET @sAlterTableDynamicSQL =
'
    SELECT @sColumns = @sColumns + [name] + N'','' FROM Tempdb.Sys.Columns WHERE [object_id] = object_id(''tempdb..' + @sTempTableName + ''')
'

PRINT (@sAlterTableDynamicSQL)
EXEC (@sAlterTableDynamicSQL)

This fails saying that I need to declare @sColumns since it does not exists (I guess variables aren't shared between connections). How can I manage to do this ? I figured I could create another temporary table to hold that CSV, but I'm thinking there should be another way.

What version of SQL server are you using and what are you expecting @sAlterTableDynamicSQL to be after it is set? — Otter
– Otter, Commented Jul 28, 2021 at 19:42
Why do you need dynamic SQL, you can pass a variable to object_id? Your dynamic SQL doesn't seem to make sense, is there something missing? — Charlieface
– Charlieface, Commented Jul 28, 2021 at 20:17
If you use sp_executesql (which is the best practice way to execute dynamic SQL), you can pass parameters in and out. — Dale K
– Dale K, Commented Jul 28, 2021 at 20:39
@Otter @sAlterTableDynamicSQL is expected to be SELECT @sColumns = @sColumns + [name] + N',' FROM Tempdb.Sys.Columns WHERE [object_id] = object_id('tempdb..#tempTable'), if the name of the table is #tempTable. @sColumns should be the list of the columns, separated by commas (for example, if there are 2 columns, ID and Name, we should have: ID,Name,. MySQL version is SQL Server 2016 — RegularNormalDayGuy
– RegularNormalDayGuy, Commented Jul 29, 2021 at 12:03
@Charlieface I need it in order to pass the name of the table that comes from the input of the procedure. I added comments to make it more clear — RegularNormalDayGuy
– RegularNormalDayGuy, Commented Jul 29, 2021 at 12:06

ilyazakharov · Accepted Answer · 2021-07-29 09:56:02Z

2

You should use sp_executesql statement. It would look like this:

DECLARE 
    @sColumns                NVARCHAR(MAX),
    @sAlterTableDynamicSQL   NVARCHAR(MAX),
    @temptableid INT = 3;
        
SELECT @sAlterTableDynamicSQL =
    N'SELECT @sColumns = STRING_AGG([name], '','') FROM Tempdb.Sys.Columns WHERE [object_id] =  + CAST(@temptableid AS VARCHAR(10))';

EXEC sp_executesql @sAlterTableDynamicSQL, N'@sColumns NVARCHAR(MAX) OUTPUT, @temptableid INT', @sColumns = @sColumns OUTPUT, @temptableid = @temptableid

SELECT @sColumns;

answered Jul 29, 2021 at 9:56

ilyazakharov

2881 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charlieface · Accepted Answer · 2021-07-29 12:13:18Z

1

You can pass through variables to dynamic SQL via sp_executesql

Note that you should always use QUOTENAME to escape object names
Also, dynamic SQL variables should always be nvarchar
You also should not use variable coalescing to aggregate, instead use STRING_AGG or FOR XML

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))

            
SET @sAlterTableDynamicSQL =
'
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N'','')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N''tempdb..' + QUOTENAME(@sNomTableTemporaire, '''') + ''');
';

PRINT (@sAlterTableDynamicSQL);

EXEC sp_executesql
  @sAlterTableDynamicSQL,
  N'@sColumns nvarchar(max) OUTPUT'
  @sColumns = @sColumns OUTPUT;

But you don't actually need dynamic SQL here at all. You can pass the table name straight to object_id()

DECLARE 
    @sColumns               AS NVARCHAR(MAX) = '',
    @sAlterTableDynamicSQL  AS NVARCHAR(MAX),
    @sGUID                  AS VARCHAR(MAX) = CAST(NEWID() AS VARCHAR(MAX))
            
SELECT @sColumns = STRING_AGG(CAST([name] AS nvarchar(max)), N',')
FROM Tempdb.sys.columns
WHERE [object_id] = object_id(N'tempdb..' + QUOTENAME(@sNomTableTemporaire));

For SQL Server 2016 and earlier, you can use the FOR XML PATH('') method

edited Jul 29, 2021 at 12:13

answered Jul 29, 2021 at 12:06

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

1 Comment

RegularNormalDayGuy Over a year ago

I'll accept this answer since it avoids using dynamic SQL. NOte to others, STRING_AGG is available only in 2017+ SQL Server version

Collectives™ on Stack Overflow

Using variables within a dynamic sql script

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related