Dynamic query in configuration table - must declare the scalar variable

Question

I have the following code:

/* Log Table */
IF OBJECT_ID('tempdb..#LogETL') IS NOT NULL
DROP TABLE #LogETL

CREATE TABLE #LogETL (
LogETL INT IDENTITY(1,1) NOT NULL,
StartTime DATETIME,
EndTime DATETIME,
JobName VARCHAR(100),
StepName VARCHAR(100),
RowsInserted INT,
RowsUpdated INT,
RowsDeleted INT
)

/* Config Table */

IF OBJECT_ID('tempdb..#LogConfigQuery') IS NOT NULL
DROP TABLE #LogConfigQuery

CREATE TABLE #LogConfigQuery (
QueryType NVARCHAR(200), 
Query NVARCHAR(1000)
)

INSERT INTO #LogConfigQuery
VALUES ('INSERT',
'INSERT INTO #LogETL
        ( StartTime ,
          EndTime ,
          JobName ,
          StepName ,
          RowsInserted ,
          RowsUpdated ,
          RowsDeleted
        )
VALUES  ( @StartTime ,
          @EndTime ,
          @JobName ,
          @StepName ,
          @RowCount ,
          0 ,
          0 
        )'
)

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

/* Declare Log Variables */

DECLARE @StartTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @JobName VARCHAR(200)
DECLARE @StepName VARCHAR(100)
DECLARE @RowCount INT

/* Declare Query */

-- insert
DECLARE @logInsert NVARCHAR(1000)
SET @logInsert = (SELECT Query FROM #LogConfigQuery WHERE QueryType = 'INSERT')
SELECT @logInsert

/* Clean Data */

SET @StartTime = (SELECT GETDATE())
SET @JobName = OBJECT_NAME(@@PROCID)
SET @StepName = 'Clean Data'

IF OBJECT_ID('tempdb..#hey') IS NOT NULL
DROP TABLE #hey
SELECT *
INTO #hey
FROM (
SELECT 'omg' omg
UNION ALL
SELECT 'omg' omg
) X

SET @RowCount = @@ROWCOUNT
SET @EndTime = (SELECT GETDATE())

/* Log Clean Data */

EXEC sp_executesql @logInsert

The purpose of the whole query above is to have one configuration table that contains query for insert logging. I want to take this query from configuration table and set it to variable @logInsert in stored procedure. Then I want to do some data cleaning in stored procedure and exec the query stored in variable (@logInsert). But it does not work this way as the error is thrown:

"Must declare the scalar variable .."

How should I modify the code so I can run dynamic query from the configuration table? I need to have this query in configuration table as I will call it in other several stored procedures, therefore I do not want to hard code it in each stored procedure as the query may change and I want to change it in one place after.

Lukasz Szozda · Accepted Answer · 2015-10-09T12:10:32.873

0

You need to pass variables to dynamic sql:

LiveDemo

EXEC dbo.sp_executesql 
   @logInsert,
   N'@StartTime DATETIME,
     @EndTime DATETIME,
     @JobName VARCHAR(200),
     @StepName VARCHAR(100),
     @RowCount INT',
   @StartTime ,
   @EndTime ,
   @JobName ,
   @StepName ,
   @RowCount ;

Explanation:

When you use Dynamic-SQL the new context is created. Your variables are in another scope and they are not accessible. You need to pass them, so you can use them.

More info in sp_executesql documentation.

edited Oct 09 '15 at 12:10

answered Oct 09 '15 at 12:02

Lukasz Szozda

162,964
23
234
275

Great, pretty easy. If you could also post some article related to passing the variables at beginner level, it would be great. Thanks. – DNac Oct 09 '15 at 12:08
@DNac I suggest official doc https://msdn.microsoft.com/en-us/library/ms188001.aspx there is everything you need to know + sample code – Lukasz Szozda Oct 09 '15 at 12:11

Dynamic query in configuration table - must declare the scalar variable

1 Answers1