Dynamic T-SQL, set the result of the query to @variable

Question

Post reply

Dynamic T-SQL, set the result of the query to @variable

waqas.ahmd

SSC Enthusiast

Points: 110
More actions
September 27, 2011 at 8:45 am

#245594

I have the following query in a variable, the result of the select is a value and i wannt it to be assigned to Percentage2012, but after its execution if @Percentage2012 variable is printed, its printed as 0. ANy body knows whats the issue??
SET @q = 'SELECT
STR(([2012] / ISNULL(NULLIF([2011],0),1) - 1),8,5) as [2012_Growth]
FROM
(
SELECT * FROM
(
SELECT Region, Year, X4_Desc, X3_Desc, ROUND(Sum(' + @Column + '),0) as [' + @Column + ']
FROM tblFinancials
WHERE Region = ''' + @Region + ''' AND X6_Desc = ''' + @X6 + ''' AND X5_Desc != X4_Desc AND year>2009 AND X3_DESC = ''' + @X3_Desc + '''
GROUP BY Region, Year, X4_Desc, X3_Desc
) as SRC
PIVOT (SUM(' + @Column + ') For Year IN ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017])) as PVT
) as SRC2'
EXEC @Percentage2012 = SP_EXECUTESQL @q, N'@Percentage2012 VARCHAR(100) OUTPUT', @Percentage2012 OUTPUT

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paul_ramster Ten Centuries Points: 1382 More actions · Answer 1

you've not set the variable "@Percentage2012" to anything in the dynamic SQL.

the OUTER select should be something like:

SELECT @Percentage2012 = STR(([2012] / ISNULL(NULLIF([2011],0),1) - 1),8,5) as [2012_Growth] ...

waqas.ahmd SSC Enthusiast Points: 110 More actions · Answer 2

if i dnt put the query in @q variable and then exec @q

i have problems of my other variables like @Column, @Region e.t.c

i tried

SET @Percentage2012 = (SELECT

STR(([2012] / ISNULL(NULLIF([2011],0),1) - 1),8,5) as [2012_Growth]

FROM

(

SELECT * FROM

(

SELECT Region, Year, X4_Desc, X3_Desc, ROUND(Sum(GP),0) as GP

FROM tblFinancials

WHERE Region = '' + @Region + '' AND X6_Desc = '' + @X6 + '' AND X5_Desc != X4_Desc AND year>2009 AND X3_DESC = 'aortic'

GROUP BY Region, Year, X4_Desc, X3_Desc

) as SRC

PIVOT (SUM(GP) For Year IN ([2010],[2011],[2012],[2013],[2014],[2015],[2016],[2017])) as PVT

) as SRC2)

but here i am providing ROUND(Sum(GP),0) -- >> GP as hard coded which i cant, so remain stuck there

paul_ramster Ten Centuries Points: 1382 More actions · Answer 3

you can still create some dynamic SQL and exec it, but you need to set the value of the variable within the dynamic SQL as I showed you above.

GSquared SSC Guru Points: 260824 More actions · Answer 4

The problem is this part:

EXEC @Percentage2012 =

When you do that, you are setting the variable to the return value of the executed procedure. Not the output parameter, the return value. If you don't specify a return value, you get the error code. Assuming sp_executesql (in this case) runs correctly, you will get the error code of 0, which means "ran without error".

You're already assigning the value in the output parameter, so just change that part to:

EXEC sp_executesql

Removing the return value assignment.

That should do it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

SDB15 SSC Eights! Points: 949 More actions · Answer 5

Another common problem I've had is where SQL will assume I want the results rounded to an INT. I usually solve the problem by adding .00 after the number in the divisor so it forces it into a FLOAT.