September 12, 2013 at 12:05 pm
Hi,
I have a question and see if people can help me out in this, I was trying to sum the column number up, but somehow if I do select " + @GrandTotalCol + " from ##temp1, it doesnot sum the column, however if I do select ISNULL([column1],0) + ISNULL ([column2],0) then it works. my @GrandTotalCol variable does print ISNULL([column1],0) + ISNULL ([column2],0). I am not sure what did I wrong. any help and clue would be appreciate.
thanks
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (ORGANIZATIONID_NAME AS nvarchar(max)) +'],0) + ',
'ISNULL([' + CAST(ORGANIZATIONID_NAME AS nvarchar(max))+ '],0) + ')
FROM
REQUESTS
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
select
reporttypename,
" + @GrandTotalCol + "
from ##temp1
September 12, 2013 at 12:53 pm
wei (9/12/2013)
Hi,I have a question and see if people can help me out in this, I was trying to sum the column number up, but somehow if I do select " + @GrandTotalCol + " from ##temp1, it doesnot sum the column, however if I do select ISNULL([column1],0) + ISNULL ([column2],0) then it works. my @GrandTotalCol variable does print ISNULL([column1],0) + ISNULL ([column2],0). I am not sure what did I wrong. any help and clue would be appreciate.
thanks
/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (ORGANIZATIONID_NAME AS nvarchar(max)) +'],0) + ',
'ISNULL([' + CAST(ORGANIZATIONID_NAME AS nvarchar(max))+ '],0) + ')
FROM
REQUESTS
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)
select
reporttypename,
" + @GrandTotalCol + "
from ##temp1
Hi and welcome to SSC!!!
It is totally unclear what you are trying to do here, however it is totally clear that this query is never going to work as posted. I am guessing that you are building a dynamic string of columns or something? You can't just stick a string in the middle of a query and expect it to execute it. You have to create your dynamic sql string and then execute it.
Also, global temp tables are generally considered to be bad practice because the results are indeterminate when more than 1 person is executing the code.
Please take a few minutes and read the first article in my signature for best practices when posting a question.
_______________________________________________________________
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/
September 12, 2013 at 1:32 pm
thank you Sir, your explanation definitely help. I will make sure the next post is follow the best practice
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply