SHARE
TWEET

Untitled

a guest Jul 6th, 2017 9 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS #12thMonthExpectedActivity
  2. DECLARE @query AS varchar(max)
  3. DECLARE @schemaName AS varchar(5) = 'wl03' -- To be used later as a parameter in stored procedure
  4. 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
  5. 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
  6.  
  7. -- Add rows for existing month into a temporary table
  8. -- The code below returns an error: The data types varchar and date are incompatible in the add operator.
  9. 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 + ''')'
  10. EXEC(@query)
  11.  
  12. -- this fails now, but it works fine if I don't do dynamic SQL
  13. select * from #12thMonthExpectedActivity
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top