Creating Procedure with Cursor doing some cal

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 2000 Forums

SQL Server Development (2000)

Creating Procedure with Cursor doing some cal

Author

Topic

sureshkumar
Starting Member

11 Posts

Posted - 2004-08-17 : 07:57:16

Hi

I want to write a procedure which is having cursor inside which will fetch some records from the two tables and calculating some values and insert in to another table. If you have any sample please send me and i will do according to that.

here i have done one cursor whether it is correct?

i want to create this cursor as procedure and do some calculation.

DECLARE @a varchar(50), @b varchar(50) @c dATETIME, @D FLOAT, @E DATETIME, @F FLOAT

DECLARE CURSORA FOR
SELECT a, b, C, D, E, F from abc ,DEF WHERE DEF.A = ABC.A AND DEF.B = ABC.B

OPEN CURSORA
FETCH NEXT FROM A INTO @A, @B, @C

WHILE (@@FETCHSTATUS =0)
BEGIN
PRINT @A
C = DATEDIFF(@C - NOW)

insert into c values('''')
FETCH NEXT FROM CURSORA INTO @A,@B,@C,@D,@E,@F
END

CLOSE CURSORA

Thanks and regards
suresh kumar.A

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-17 : 08:01:45

You don't want to have a cursor. You want to find a set-based way to do this. It's a set-based language. I'm Obi-Wan and I'm now passing my hand in front of your face. You will now search this forum for cursor and find out 1 million reasons you shouldn't use them.

Feel the force suresh. It surrounds us. It binds us together. It doesn't suck like cursors.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-17 : 08:10:14

midichlorians in their purest :))))))))

Go with the flow & have fun! Else fight the flow :)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-17 : 08:10:24

LOL, derrick!! Could be a nice dialog in the next installment of SQL Wars

Hey what would you call a sequel to SQL Wars then?

Suresh, your cursor doesn't really do anything. Tell us what you want to do and give us the table structures and we should be able to help you do this without cursors.

OS

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-17 : 08:19:27

Hey what would you call a sequel to SQL Wars then?

The Oracle Strikes Back

followed by

Return of the DBA who likes to get things done instead of being part of the elitist good boys club and impeding process for the sake of my job.

DOH!!!

Sorry, I used to work with Oracle a lot. So happy...so happy...I so happy.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sureshkumar
Starting Member

11 Posts

Posted - 2004-08-17 : 09:25:01

Thanks for ur suggestion and i new to sql server database.
i set based program will work for me.

dasu
Posting Yak Master

104 Posts

Posted - 2004-08-17 : 09:46:07

here iam giving small example for nested cursers ,i hope u need it

" -- Declare host variables

DECLARE @ID varchar(30), @nam varchar(30),@in varchar(30)

-- Declare the cursor

DECLARE Myinfo CURSOR LOCAL
FORWARD_ONLY
FOR
SELECT id , name
FROM test

-- Open the cursor

OPEN Myinfo

-- Fetch the first row in the cursor

FETCH NEXT FROM Myinfo
INTO @ID, @nam
WHILE @@FETCH_STATUS = 0

-- While the fetch is successful

BEGIN

DECLARE Mytt CURSOR LOCAL
FOR
SELECT ino
FROM tttt

-- Open Orders cursor

OPEN Mytt

-- Search for first Order

FETCH NEXT FROM Mytt
INTO @in

WHILE @@FETCH_STATUS=0
BEGIN
if (@in=@ID)
print 'the request all ready exists'
else
insert into test values(@in,'dasu')
FETCH NEXT FROM Mytt
INTO @in

END

-- Close Orders cursor

CLOSE Mytt

--Deallocate Orders cursor

DEALLOCATE Mytt

FETCH NEXT FROM Myinfo
INTO @ID, @nam

END

-- Close the cursor

CLOSE Myinfo

-- Deallocate the cursor

DEALLOCATE Myinfo

"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-17 : 09:47:03

He doesn't need it. That's one of the worst things you can do in SQL. Gheesh.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources