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!