0

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!

1

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.

  • Thanks! But now I got another error, which I was expecting: The data types varchar and date are incompatible in the add operator. – pmdci Jul 6 '17 at 23:33
  • Ugh! I am still having the same error :( pastebin.com/QbrvZuRR – pmdci Jul 6 '17 at 23:46
  • Doh! My bad. The code seems to work now but if I try to do select * from #12thMonthExpectedActivity it tell me that the object does not exist?! what the heck... :( pastebin.com/mgyDCfJy – pmdci Jul 6 '17 at 23:55
  • Oh crap. dynamics SQL is such a PAIN! Problem is that after I create the temp table, I need to insert those values on another table, and that is another statement! how can I ensure the table keeps? – pmdci Jul 6 '17 at 23:58
  • Let us continue this discussion in chat. – Randolph West Jul 6 '17 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.