declaring variable

Question

Post reply

declaring variable

DuruG

SSCrazy

Points: 2089
More actions
October 25, 2002 at 6:42 am

#80250

Hi everybody:
I have this dynamic select statement:
declare @comp_failed as int
SET @strSQL = 'SELECT @comp_failed=SUM(FAILED) FROM TIME_VW WHERE ' + @STR
exec(@strSQL)
and is telling me variable @comp_failed is not declared, which is not true.
all others variable from the @strSQL are declared.
Any saving ideas?
Thanks a lot,
Durug

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

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

Emma Smyth Old Hand Points: 315 More actions · Answer 1

Try initializing your variable before calling it.

Eg.

declare @comp_failed as int

@comp_failed = SUM(FAILED) from TIME_VW

set @strSQL = 'SELECT @comp_failed

where' + @STR

Emma Smyth

ESmyth

DuruG SSCrazy Points: 2089 More actions · Answer 2

Hi Emma,

sorry but it doesn't work. if I use your syntax i'm receiving

'Incorrect syntax near '@comp_failed' probably because of the set keyword missing.

if I add the set I'm getting error next to from and so on.

Any other ideas?

Thanks a lot.

paulhumphris Hall of Fame Points: 3496 More actions · Answer 3

Your variable @comp_failed is out of scope when referenced from the dynamic SQL. Dynamic SQL statements run in a separate batch and cannot therefore see local variables.

Try concatenating the value of the variable into the @strSQL statement before execution.

declare @comp_failed int

set @strSQL = 'SELECT ' + convert(varchar(15), @comp_failed) + '=SUM(Failed) FROM TIME_VW WHERE ' + @STR

exec (@strSQL)

DuruG SSCrazy Points: 2089 More actions · Answer 4

Thanks. I'm not getting any error, but now after executing the SQL statement

and trying to print the @comp_failed I have no value in it.

Is it becasue the conversion, or what??? 🙂

Thanks a lot,

Durug

paulhumphris Hall of Fame Points: 3496 More actions · Answer 5

Now I see the problem, I missed the fact that you were trying to return the result into the declared variable.

The only way I can think of doing this is with temporary tables. You can use temporary tables within dynamic SQL because their scope is against the connection and not the individual procedure.

declare @comp_failed int

declare table #tmpResult (CompFailed int)

set @strSQL = 'SELECT Sum(Failed) as CompFailed FROM Time_VW WHERE ' + @STR

insert into #tmpResult (CompFailed)

exec (@strSQL)

set @comp_failed = (SELECT TOP 1 CompFailed FROM #tmpResult)

drop table #tmpResult

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Just for testing purposes, have you tried it without the variable?

SET @strSQL = 'SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR

exec(@strSQL)

I know you need to use the variable, probably for additional script, but if you test just the above line you can tell if the problem is with the variable portion.

-Bill

DuruG SSCrazy Points: 2089 More actions · Answer 7

BillSQL: it works if I eliminate the variable from the @strSQL

Paul: this is the solution that I used before, but I was trying to avoid the temp tables.

Thanks a lot all of you.

Durug

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Have you tried this?

declare @comp_failed as int

SET @strSQL = 'SET @comp_failed = SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR

exec(@strSQL)

-Bill

SQLBill SSC Guru Points: 51440 More actions · Answer 9

OOOPPS, I made a mistake....try this (note parenthesis' around the select statement):

declare @comp_failed as int

SET @strSQL = 'SET @comp_failed = (SELECT SUM(FAILED) FROM TIME_VW WHERE ' + @STR)

exec(@strSQL)

-Bill

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 10

EXEC performs the SQL in a different scope to the calling TSQL, you can therefore not use variables declared outside of it.

You need to use sp_executesql

declare @comp_failed as int

SET @strSQL = 'SELECT @comp_failed = SUM(FAILED) FROM TIME_VW WHERE ' + @STR

exec sp_executesql @strSQL, N'@comp_failed int OUTPUT', @comp_failed OUTPUT

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons