How to retain the variable's value in a dynamic sql? Thanks.

Question

Post reply

How to retain the variable's value in a dynamic sql? Thanks.

halifaxdal

SSCoach

Points: 19741
More actions
May 17, 2012 at 10:19 am

#258401

In a case I have to use dynamic sql in my query, here is the query:
set @sql = 'select @subTotal = count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'
exec (@sql)
there is no problem with the @sql, but, after it is executed, I can't get @subTotal, I guess the variable's value didn't get retained after the exec, how do I retain the value?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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

halifaxdal SSCoach Points: 19741 More actions · Answer 1

Tried this, also failed:

EXEC sp_executesql

@query = @sql,

@params = N'@subTotal VARCHAR OUTPUT',

@subTotal = @subTotal OUTPUT

Error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

But I do need the @subTotal be int

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

You should declare your @sql variable as NVARCHAR (not varchar)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Perhaps this:

set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'

DECLARE @SubTotalT TABLE (subTotal INT),

@subtotal int;

INSERT INTO @SubTotal

exec (@sql);

select @subtotal = subTotal from @SubTotalT;

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Thank you very much Lynn. I will try out your solution.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

halifaxdal (5/18/2012)
Thank you very much Lynn. I will try out your solution.

Slight change, needed to add a second column to the table variable. You may need to adjust the size of the second column based on your data.

set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'

DECLARE @SubTotalT TABLE (subTotal INT, sa varchar(100),

@subtotal int;

INSERT INTO @SubTotal

exec (@sql);

select @subtotal = subTotal from @SubTotalT;

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Eugene Elutin (5/17/2012)
You should declare your @sql variable as NVARCHAR (not varchar)

That's not where is the problem, but thank you for your reply.

halifaxdal SSCoach Points: 19741 More actions · Answer 7

Lynn Pettis (5/17/2012)
Perhaps this:
set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'
DECLARE @SubTotalT TABLE (subTotal INT),
@subtotal int;
INSERT INTO @SubTotal
exec (@sql);
select @subtotal = subTotal from @SubTotalT;

It definitely works! Thank you. Only some typos in your code. 😉

set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'

DECLARE @SubTotalT TABLE (subTotal INT)

INSERT INTO @SubTotalT exec (@sql)

select @subtotal = subTotal from @SubTotalT

Why your code has comma? my SQL 2005 doesn't accept that. :hehe:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

halifaxdal (5/18/2012)
Lynn Pettis (5/17/2012)
Perhaps this:
set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'
DECLARE @SubTotalT TABLE (subTotal INT),
@subtotal int;
INSERT INTO @SubTotal
exec (@sql);
select @subtotal = subTotal from @SubTotalT;
It definitely works! Thank you. Only some typos in your code. 😉
set @sql = 'select count(1), sa from deviationbycategoryandsa where [' + @Category + '] = 1 and sa = ' + '''' + @sa + '''' + ' group by sa'
DECLARE @SubTotalT TABLE (subTotal INT)
INSERT INTO @SubTotalT exec (@sql)
select @subtotal = subTotal from @SubTotalT
Why your code has comma? my SQL 2005 doesn't accept that. :hehe:

I terminated my SQL Statements with semicolons (;), and it works just fone for me on my SQL Server 2005 systems.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

You can make your life a bit easier if you can do this using sp_ExecuteSQL. It was built for this kind of stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Tried this, also failed:
EXEC sp_executesql
@query = @sql,
@params = N'@subTotal VARCHAR OUTPUT',
@subTotal = @subTotal OUTPUT
Error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
But I do need the @subTotal be int

halifaxdal (5/18/2012)
Eugene Elutin (5/17/2012)
You should declare your @sql variable as NVARCHAR (not varchar)
That's not where is the problem, but thank you for your reply.

The problem (the error) you had is caused by wrong datatype of the variable you supplied as first parameter into extended sp_executesql stored proc.

Using EXEC instead of sp_executesql didn't really fixed anything as it just used another technique to get the value out of dynamic sql. However, many folk here would agree that using sp_executesql over EXEC is the preferable option.

Yes, in your case the datatype of @sql variable wasn't the only problem, as you had wrong type (varchar instead of int or at least varchar(10)) defined for your output parameter as well. But the posted error you had is only related to @sql variable datatype, after fixing this one you might have the error about datatype conversion (if count of records is greater than 9)

Try the following samples:

DECLARE @sql VARCHAR(100)

DECLARE @subTotal INT

SET @sql = 'set @subTotal = 10'

EXEC sp_executesql @query = @sql, @params = N'@subTotal varchar OUTPUT', @subTotal = @subTotal OUTPUT

SELECT @subTotal

Will produce:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

It's a bit of misleading to see complain about @statement parameter, as there isn't one. But, that is the nature of extended sps. You can even try:

EXEC sp_executesql @Idoncarewhat = @sql, @params = N'@subTotal varchar(10) OUTPUT', @subTotal = @subTotal OUTPUT

It will also produce the same error until you change @sql to be nvarchar in which cae it will work just fine. In MS documentation, the first parameter is called @stmt. But Sql Server doesn't need parameter name matched...

The next try:

DECLARE @sql NVARCHAR(100)

DECLARE @subTotal INT

SET @sql = 'set @subTotal = 10'

EXEC sp_executesql @query = @sql, @params = N'@subTotal varchar OUTPUT', @subTotal = @subTotal OUTPUT

SELECT @subTotal

Will produce:

Msg 8114, Level 16, State 2, Line 0

Error converting data type varchar to int.

As @params = N'@subTotal varchar OUTPUT' only allows varchar(1) in @subTotal, converting of 10 is failing.

And the last one:

DECLARE @sql NVARCHAR(100)

DECLARE @subTotal INT

SET @sql = 'set @subTotal = 10'

EXEC sp_executesql @query = @sql, @params = N'@subTotal varchar(10) OUTPUT', @subTotal = @subTotal OUTPUT

SELECT @subTotal

The above works as varchar(10) is enough to accommodate any positive INT.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]