T-SQL – How to Create Dynamic SQL Statements with Variables

dynamic-sqlt-sql

I am having a real hard time trying to convert a TSQL statement (which be a store procedure) into a Dynamic SQL. Basically I must be able to pass the schema name as a parameter.

Here is my code:

DECLARE @query AS varchar(max)
DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE) --The 11th month from the current month
DECLARE @plus12Month AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE) --The 12th month from the current month

DROP TABLE IF EXISTS #12thMonthExpectedActivity

-- Add rows for existing month into a temporary table
-- The code below returns an error: The data types varchar and date are incompatible in the add operator.
SET @query = 'SELECT ' + @plus12Month + 'AS period, genusId, subjectId, waitingStageId, value, ' + GETDATE() + ' AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName + '].[ExpectedActivity]
WHERE period in(' + @plus11Month + ')'
-- EXEC(@query) will go here once the above is working

/* This original code works just fine!
SELECT @plus12Month AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated>' AS savedBy INTO #12thMonthExpectedActivity FROM [wl03].[ExpectedActivity]
  WHERE period in(@plus11Month)
*/

The problem I have is defining the @query variable. I keep getting the same error:

Msg 402, Level 16, State 1, Line 9 The data types varchar and date are
incompatible in the add operator.

The code does work fine as long as I don't try to save it in a query. Any ideas? Thanks!

Best Answer

Your dates must be strings too, but in a format that is unambiguous (e.g. date format 112):

DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+11, 0) AS DATE), 112) --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+12, 0) AS DATE), 112) --The 12th month from the current month

You will need to convert your GETDATE() as well, where you have it in the string. Watch out for quotation marks, and casting back to DATE as well. Here's a proposed solution:

DECLARE @query AS VARCHAR(MAX);
DECLARE @schemaName AS VARCHAR(5) = 'wl03'; -- To be used later as a parameter in stored procedure
DECLARE @plus11Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 11, 0) AS DATE), 112); --The 11th month from the current month
DECLARE @plus12Month AS CHAR(8) = CONVERT(CHAR(8), CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 12, 0) AS DATE), 112); --The 12th month from the current month
DROP TABLE IF EXISTS #12thMonthExpectedActivity;

-- Add rows for existing month into a temporary table
SET @query
    = 'SELECT CAST(''' + @plus12Month + ''' AS DATE) AS period, genusId, subjectId, waitingStageId, value, CAST('''
      + CONVERT(CHAR(8), GETDATE(), 112)
      + ''' AS DATE) AS savedOn, ''<Automated>'' AS savedBy INTO #12thMonthExpectedActivity FROM [' + @schemaName
      + '].[ExpectedActivity] WHERE period in (''' + @plus11Month + ''')';

PRINT @query;

Read more about CAST and CONVERT with Books Online.

Related Question