December 19, 2013 at 7:05 pm
I have Dynamic SQL below. When I attempt to add the variable @QUEUE. I receive the following error:
"INVALID COLUMN NAME 'Chapter_Revenue'
If I remove @QUEUE from the Dynamic SQL it executes
DECLARE @tblName NVARCHAR(120)
DECLARE @sql VARCHAR(4000)
DECLARE @DATE Date
DECLARE @QUEUE nvarchar(200)
SET @DATE = '11/25/2013'
SET @QUEUE = 'Chapter_Revenue'
SET @tblName = (SELECT e.tablename
FROM BUSINESSPROCESSSTATUS a
INNER JOIN BUSINESSPROCESSCATALOG b ON a.BUSINESSPROCESSCATALOGID = b.ID
INNER JOIN POSTTOGLPROCESSSTATUS c ON c.id = a.id
INNER JOIN POSTTOGLPROCESS d ON c.PARAMETERSETID = d.id
INNER JOIN BUSINESSPROCESSOUTPUT e ON e.BUSINESSPROCESSSTATUSID = a.ID
WHERE b.NAME LIKE '%POST%' and a.NUMBERPROCESSED <> 0
and DATEADD(dd,DATEDIFF(dd,0,a.STARTEDON),0) = @DATE)
SET @sql = 'SELECT ' +'' + @QUEUE + '' + ' GLDATE, BATCHNUMBERVNEDBT, DOCUMENTNUMBER, ACCOUNTNUMBER, SUBLEDGER
, sum((CASE WHEN Cast(AMOUNT as numeric(10,2)) >= 0 THEN Cast(AMOUNT as numeric(10,2)) ELSE 0 END)) /100 AS DEBIT,
sum((CASE WHEN Cast(AMOUNT as numeric(10,2)) < 0 THEN Cast(AMOUNT as numeric(10,2))*-1 ELSE 0 END))/100 AS CREDIT
FROM ' + @tblName + ' GROUP BY GLDATE,
BATCHNUMBERVNEDBT,
DOCUMENTNUMBER,
ACCOUNTNUMBER,
SUBLEDGER
ORDER BY GLDATE'
EXEC (@SQL)
December 20, 2013 at 1:01 am
What you did results in the query being
select Chapter_Revenue GLDATE ...
And I think it should be
select 'Chapter_Revenue' GLDATE ...
Solution
SET @sql = 'SELECT ''' + @QUEUE + ''' GLDATE, BATCHNUMBERVNEDBT, DOCUMENTNUMBER, ACCOUNTNUMBER, SUBLEDGER
, sum((CASE WHEN Cast(AMOUNT as numeric(10,2)) >= 0 THEN Cast(AMOUNT as numeric(10,2)) ELSE 0 END)) /100 AS DEBIT,
sum((CASE WHEN Cast(AMOUNT as numeric(10,2)) < 0 THEN Cast(AMOUNT as numeric(10,2))*-1 ELSE 0 END))/100 AS CREDIT
FROM ' + @tblName + ' GROUP BY GLDATE,
BATCHNUMBERVNEDBT,
DOCUMENTNUMBER,
ACCOUNTNUMBER,
SUBLEDGER
ORDER BY GLDATE'
December 20, 2013 at 8:09 am
You better read up on sql injection. This code is very vulnerable.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply