stored procedure help - 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

SQL Server 2000 Forums

SQL Server Development (2000)

stored procedure help

Author

Topic

phaze
Starting Member

42 Posts

Posted - 2005-03-15 : 12:57:59

I need some advice on how to create a stored procedure using a cursor to iterate through a table. any suggestions or tips would be greatly appreciated. I'm really new to sql.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-15 : 13:03:42

Don't use a cursor if at all possible.

What are you trying to get.

Please give some data and expected results.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 13:24:02

Help us out here...what's your background

Brett

8-)

phaze
Starting Member

42 Posts

Posted - 2005-03-15 : 14:20:34

There is no data given and no specific results. I just wanted to see an example of how it would look like. My background in SQL is still new. I just started using this for about a month now. If you guys could explain and show me how to do this it would be appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 14:23:27

No, what is your other background in IT.

Do you have SQL Server Client tools installed yet?

Do you have a server you can register?

CREATE PROC mySproc99
AS
SELECT OrderId FROM Orders
GO

There's a stored procedure

Brett

8-)

phaze
Starting Member

42 Posts

Posted - 2005-03-15 : 14:40:42

yes i do have sql server installed. I am currently playing around with the northwind database. sorry i didn't understand your question. cheers! now can you possibly give me some help? = P

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-15 : 14:46:51

He did. He provided an example of a stored procedure. If you have specific questions, please let us know.

Tara

phaze
Starting Member

42 Posts

Posted - 2005-03-15 : 15:02:59

I needed help with creating a stored procedure that uses a cursor to iterate through a table. that was my original question. please help?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 15:05:23

Do you know what Query Analyzer is?

Cut and past my code that I gave you and execute it there.

What's with all the cursor talk.

Did someone tell you that you need one?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-15 : 15:11:07

quote:
Originally posted by phaze

I needed help with creating a stored procedure that uses a cursor to iterate through a table. that was my original question. please help?

Do NOT use cursors! These are very, very, very, bad for performance. Learn from us now that they are bad. Do not even learn how to write them as you won't need to use them. Your thinking needs to switch to set-based rather than iterating through a table.

Tara

phaze
Starting Member

42 Posts

Posted - 2005-03-15 : 15:19:48

ok so you say cursors are bad, i respect that, but isn't this a forum to help individuals that ask for a specific thing? i asked for help to show me how to do it. not to tell me why i shouldn't be doing it. so if you can help please do so and don't tell me what i should and shouldn't be doing. I just want to see how it would look.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-15 : 15:21:15

Then open up SQL Server Books Online and check out the examples of cursors. Why should we duplicate what is already in the documentation? Always check the documentation first.

Make sure to remember when you develop your application using cursors that we are available for hire as contractors to fix your performance problems.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 15:41:59

I get these vivid images....loaded gun...kids..


USE Northwind
GO

SET NOCOUNT ON
GO

CREATE PROC mySproc99
AS
  BEGIN
	SET NOCOUNT ON
	DECLARE myCursor99 CURSOR FOR
		SELECT o.OrderId, o.CustomerID, d.UnitPrice, d.Quantity 
		  FROM Orders o 
	    INNER JOIN [Order Details] d 
		    ON o.OrderId = d.OrderId

	DECLARE @OrderId int, @CustomerID nchar(5), @UnitPrice money, @Quantity smallint

	OPEN myCursor99
	FETCH NEXT FROM myCursor99 INTO @OrderId, @CustomerID, @UnitPrice, @Quantity
	SELECT @OrderId AS OrderId, @CustomerID AS CustomerID, @UnitPrice AS UnitPrice, @Quantity AS Quantity 
	  INTO myNewTable99 WHERE 1=0

	WHILE @@FETCH_STATUS = 0
	  BEGIN
		INSERT INTO myNewTable99 (OrderId, CustomerID, UnitPrice, Quantity) 
		     SELECT @OrderId, @CustomerID, @UnitPrice, @Quantity
		FETCH NEXT FROM myCursor99 INTO @OrderId, @CustomerID, @UnitPrice, @Quantity
	  END
	
	CLOSE myCursor99
	DEALLOCATE myCursor99
	SET NOCOUNT OFF
  END
GO

EXEC mySproc99

SELECT * FROM myNewTable99
GO

SET NOCOUNT OFF
DROP PROC mySproc99
DROP TABLE myNewTable99
DROP TABLE myTable99
GO

Brett

8-)

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-15 : 16:06:18

Homework detector

Beep
Beep
Beep

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-15 : 16:14:24

How hard is it to RTFM to see examples? If this is for class, then why is it that these students aren't aware of Books Online? Why isn't the teacher showing them the most important place to find answer?!!

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-15 : 16:28:52

quote:
Originally posted by tduggan

How hard is it to RTFM to see examples? If this is for class, then why is it that these students aren't aware of Books Online? Why isn't the teacher showing them the most important place to find answer?!!

Tara

LOL....what type of weapons do you have up there in Olumpus?

I liked the part about cleaning up the mess so we could all make some spare change....

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources