Undeclare a local variable?

1 Jul 2005 7:17 AM

Andy Bretl

Hi All,
I have a quick question regarding the use of local variables.
Actually it is more a cosmetic problem than a real one but I just wondered
if it is possible to:
UNDECLARE / REDECLARE a local variable?

ALTER doesn't work and DEALLOCATE is for cursors only...

Any ideas are kindly appreciated,

Seize the Day

Andy
(andreas.br***@brainlab.com)

1 Jul 2005 7:25 AM

Jens Süßmeyer

No, it is "Undeclared" when the scope is left.

HTH; Jens Süßmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Andy Bretl" <andreas.br***@brainlab.com> schrieb im Newsbeitrag
news:42c4ee07$0$18650$14726298@news.sunsite.dk...
> Hi All,
> I have a quick question regarding the use of local variables.
> Actually it is more a cosmetic problem than a real one but I just wondered
> if it is possible to:
> UNDECLARE / REDECLARE a local variable?
>
> ALTER doesn't work and DEALLOCATE is for cursors only...
>
> Any ideas are kindly appreciated,
>
> Seize the Day
>
> Andy
> (andreas.br***@brainlab.com)
>
>

1 Jul 2005 7:40 AM

Andy Bretl

First of all, thank you Jens for the quick reply.
I thought the scope would be left when the WHILE-loop ends.

Actually it is a big batch that uses several cursors for manipulating data.
the variables are declared in the
    WHILE @@FETCH_STATUS = 0
        BEGIN
                DECLARE @chvVar1 VARCHAR(255)
                ....
        END
block.

I thought about declaring them for every cursor but as mentioned the system
reminds me about having already a declaration from the cursor-block above.

So if I understood it right, the WHILE block is not the scope but the whole
batch.. right?

Seize the Day

Andy
(andreas.br***@brainlab.com)

1 Jul 2005 8:03 AM

Jens Süßmeyer

Yes, you are right, the problem is the compilation of the procedure or the
batch as a whole, the compiler realizes that you already declared a variable
with that name an´d will throw an error. Unfortunatley there is no way
around that unless you put the inner logic in another procedure or funtion
with executes on another scope.

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"Andy Bretl" <andreas.br***@brainlab.com> schrieb im Newsbeitrag
news:42c4f383$0$18644$14726298@news.sunsite.dk...
> First of all, thank you Jens for the quick reply.
> I thought the scope would be left when the WHILE-loop ends.
>
> Actually it is a big batch that uses several cursors for manipulating
> data.
> the variables are declared in the
>    WHILE @@FETCH_STATUS = 0
>        BEGIN
>                DECLARE @chvVar1 VARCHAR(255)
>                ....
>        END
> block.
>
> I thought about declaring them for every cursor but as mentioned the
> system
> reminds me about having already a declaration from the cursor-block above.
>
> So if I understood it right, the WHILE block is not the scope but the
> whole
> batch.. right?
>
> Seize the Day
>
> Andy
> (andreas.br***@brainlab.com)
>
>

1 Jul 2005 8:11 AM

Andy Bretl

Hi Jens,
thank you very much for the explanation.
I'll take your idea and divide the batch into different procedures. I guess
taht will be better to overlook without thinking about different
variable-names for equal columns.

Thanks again and have a great weekend

Andy