Declaring a variable

[durug]

Hi everybody:
I have this dynamic select statement:
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.


Any ideas?

Thanks,
Durug

 

[sunila7]

Hi
Try this

Declare @compl_falied int

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

exec(@strSQL)


Sunil

 

[durug]

Excuse me, but I don't see the difference between what you respond and what I wrote.
As I said I declared the variable @comp_failed

Thanks anyway.
Other ideas?

Durug

 

[sunila7]

Hi,
Try this....
I couldn't think of any other soln other than to use a temp table

Declare @compl_falied int

SET @strSQL = 'SELECT SUM(FAILED) sumFailed FROM TIME_VW INTO #temp1 WHERE ' + @str

exec(@strSQL)

Select @compl_failed=sumFailed from #temp1

Sunil

 

[SQLBill]

durug,

You say you DECLAREd @compl_failed, where did you do that? The script you posted doesn't have a DECLARE statement. You are ASSIGNING SUM(FAILED) to the variable @compl_failed, but the procedure doesn't know yet what @compl_failed is. It needs to be DECLAREd as an integer.

-SQLBill

 

[chrisv71]

Unless I'm mistaken, it seems to me that you can't use the 'EXEC' function for dynamic SQL with variables. SQL treats the EXEC statement as it's own private batch so it can't see any variable declarations outside the specific dynamic SQL statement. I think you might be able to use 'sp_executesql' to execute dynamic SQL with variables but I'm no expert on dynamic SQL... hope it helps.

 

[MeanGreen]

Your variable is loosing scope in the execute command. You might try this:

SET @strSQL = 'Declare @comp_failed integer SELECT @comp_failed=SUM(FAILED) FROM TIME_VW WHERE ' + @str + ' select @comp_failed'

exec(@strSQL)


Hope this helps.

 

[tlbroadbent]

Use sp_executesql to return a value from a dynamically executed statement. This overcomes the problem of the out of scope variable.

Declare @comp_failed int

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

EXEC sp_executesql
@strSQL,
N'@cf int',
@cf=@comp_failed output

Note: I used different names for the variables in order to differentiate but you can use the same name for the sp_executesql parameter as is used in the SP. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.