problems with variable assignment in sql procedure

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

SQL Server 2005 Forums

Transact-SQL (2005)

problems with variable assignment in sql procedure

Author

Topic

Labhesh
Starting Member

1 Post

Posted - 2007-09-06 : 15:20:00

Hi Everyone:

I am writing a code where i want the structure to be as follows:

fetch from cursor into @variable1
fetch next from cursor into @variable2
if(@variable1 < @variable2)
{
@variable1 = @variable2
......and so on......
}

I am new to writing procedures in sql and thus cant figure out a way to covert "usual" if programming stmnts into the sql procedure ifs...
the same is true with for statements...
I am trying:

for(i = 0; i <= @@CURSOR_ROWS; i++)

it gives me a syntax error.
Can someone tell me to use the above 2 statements to achieve my goal?

Best Regards;
Labhesh.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 15:21:55

T-SQL has only While loop
while (condition)
begin
... stuff to do
end

but do you really need to loop??

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 15:30:24

cursor is also available...check BOL on how to use

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-06 : 15:33:53

you just had to say it didn't you.
and i was so trying to avoid mentioning cursors

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 15:40:01

awww don't be bias Mladen... he has the right to know all options available

both works the same... it's not the method but how you use it

PS... in a free-for-all mode (CnC players hail!)... all advises given out are with 'use at your own risk' flags

--------------------
keeping it simple...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-06 : 17:29:34

quote:
Originally posted by jen

both works the same... it's not the method but how you use it

Actually, They are NOT the same and I would advocate that one NEVER EVER use a cursor in a production system.

Yes, you can mess up a loop just like a curosor. However, loops are not memory allocating objects like cursors that can eat up your system memory and not return it to the heap if not deallocated properly (Caveat Cursor). Now, granted if you deallocate properly and such it shouldn't be any issue. But, why take the chance?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-09-06 : 17:49:58

If you really want all options...


Start_Loop:
if <some condition is no longer true> goto End_Loop
... do stuff
... do more stuff
... do even more stuff
goto Start_Loop
End_Loop:

CODO ERGO SUM

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-09-06 : 22:40:17

quote:
Originally posted by Lamprey

quote:
Originally posted by jen

both works the same... it's not the method but how you use it

Actually, They are NOT the same and I would advocate that one NEVER EVER use a cursor in a production system.

Yes, you can mess up a loop just like a curosor. However, loops are not memory allocating objects like cursors that can eat up your system memory and not return it to the heap if not deallocated properly (Caveat Cursor). Now, granted if you deallocate properly and such it shouldn't be any issue. But, why take the chance?

I believe there are cases where using a cursor is the most efficient method such as calculating FIFO cost.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources