how to make @str global? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to make @str global?

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-01-31 : 11:32:07

If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.

I guess I have to execute part1 and Q2 at same time.

Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank

/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';

/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str

/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-31 : 11:44:56

No.

You will have to execute query containing variable along with variable definition code.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-31 : 16:03:09

declare @str nvarchar(20);
set @str='%subway%';
select s = @str into #a

/*Q1*/
select a.*, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails a
join #a
on businessname like #a.s

/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
join #a
on businessname like #a.s
group by contracttypename,regionname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jeff06
Posting Yak Master

166 Posts

Posted - 2007-02-08 : 11:14:54

NR, That is awesome, Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources