using a variable outside the scope it was declared

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

using a variable outside the scope it was declared

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-01-13 : 10:44:08

hopefully this is a simple question. how do you use a variable outside it's local scope? for example:


declare @qty int = 10

select top (@qty) * into #temp from main.dbo.mytable

alter table #temp add column uid int identity(1,1)
go

delete from #temp where uid between 1 and 5
if @qty =5
  exec dba.dbo.mysp '#temp'

i ask because i cant do the delete in the same batch as the alter, because SQL doesnt think that field exists yet, so when it parses the query, it throws and error...

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 10:47:52

the go causes first batch to end and variable to be out of scope. remove it and it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-13 : 11:04:36

select top (@qty) *, identity(int,1,1) as uid into #temp from main.dbo.mytable

With the given syntax you don't need to do an alter table.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-13 : 11:06:01

quote:
Originally posted by visakh16

the go causes first batch to end and variable to be out of scope. remove it and it will work fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

You have not read properly - OP already knows that

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-13 : 11:10:55

Oh ok...I understand

But if your id column is just for sake of deletion no need of adding it to table. you can do delete on the fly like


declare @qty int = 10


delete t
from
(select top (@qty) row_number() over (order by newid()) as rn from main.dbo.mytable
)t
where rn <= 5

...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-01-13 : 11:16:45

yeah, i know i can do that, this is more of an example than the actual case...

is there any way to retain the variable after a go, or another way to "go" with out it losing the var?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-13 : 11:18:34

No, you're never too old to Yak'n'Roll if you're too young to die.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-01-13 : 11:46:19

FAIL! lol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources