stored procedure and looping - 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 2005 Forums

Transact-SQL (2005)

stored procedure and looping

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-21 : 13:36:49

i'm trying to translate some asp code I wrote to an sql server stored procedure (as i think it's much better then the mulitple queries) and i'm trying to figure out the best way.

what i need to do is loop through an sql statement and with that statement take the record and update 3 other tables with info from that record --

how can i do this kind of loop in sql server

For example

select * from orders where mydate<getdate()-1

loop through this recordset and do

insert into neworders (id,name,amount) values(id,name,amount (from orders)

update orders set inserted=1

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-21 : 13:47:13

Actually you might not need to loop through for that.


INSERT INTO neworders (id,name,amount)
SELECT id,name,amount from orders
where mydate<getdate()-1

Then you can run a batch update on Orders


Update Orders
Set Inserted =1
From Orders O
JOIN neworders NeO ON O.Id = NeO.Id -- > add additional columns if necessary.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-21 : 13:50:15

insert into neworders (id,name,amount)
select id,name,amount
from orders
where mydate < DATEADD(dd,DATEDIFF(dd,0,DATEADD(dd,-1,@Date)),0)

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 13:51:54

INSERT INTO neworders (id,name,amount,inserted)
SELECT id,name,amount, 1 from orders
where mydate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-21 : 13:59:23

these technically work but at the same time as inserting in neworders i have to update that order that it was put in new orders

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 14:01:11

What's wrong with Peso's solution?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-21 : 14:09:10

here



DECLARE myCursor99 CURSOR
AS 
SELECT id,name,amount FROM Orders WHERE mydate<getdate()-1

DECLARE @id int, @name varchar(50), @amount money
OPEN myCursor99
FETCH NEXT INTO @id, @name, @amount 

WHILE @@FETCH_STATUS = 0
  BEGIN
	loop through this recordset and do
	insert into neworders (id,name,amount) values(@id,@name,@amount)
	update orders set inserted=1 WHERE id = @id
	FETCH NEXT INTO @id, @name, @amount 
  END

CLOSE myCursor99
DEALLOCATE myCursor99

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 14:20:34

Or the new features of SQL Server 2005?

CREATE TABLE Orders (ID INT, Name SYSNAME, Amount INT, MyDate SMALLDATETIME, Inserted TINYINT)

INSERT	Orders
SELECT	1, 'Peso', 99, '20060101', 0 UNION ALL
SELECT	2, 'Jeff', 100, CURRENT_TIMESTAMP, 0 UNION ALL
SELECT	3, 'Brett', 199, '20070101', 1

CREATE TABLE NewOrders (ID INT, Name SYSNAME, Amount INT)

select * from orders
select * from neworders

UPDATE	o
SET	o.Inserted = 1
OUTPUT	INSERTED.ID,
	INSERTED.Name,
	INSERTED.Amount
INTO	NewOrders
FROM	Orders AS o
WHERE	o.MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND o.Inserted = 0

select * from orders
select * from neworders

drop table orders
drop table neworders

I hope you are satisfied now?

E 12°55'05.25"
N 56°04'39.16"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-21 : 14:52:43

Have you in fact got any cheese at all?

http://www.minderella.com/words/cheeseshop.htm

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 15:00:35

Or, if you prefer two queries

INSERT	NewOrder
	(
		ID,
		Name,
		Amount
	)
SELECT	ID,
	Name,
	Amount
FROM	Orders
WHERE	MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND Inserted = 0

UPDATE	Orders
SET	Inserted = 1
WHERE	MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND Inserted = 0

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-21 : 16:08:41

quote:

Originally posted by Peso

Or, if you prefer two queries

INSERT	NewOrder
	(
		ID,
		Name,
		Amount
	)
SELECT	ID,
	Name,
	Amount
FROM	Orders
WHERE	MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND Inserted = 0

UPDATE	Orders
SET	Inserted = 1
WHERE	MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND Inserted = 0

E 12°55'05.25"
N 56°04'39.16"

Peter you might want to join back to NewOrders table when updating Orders with 0, just in case there are orders already in Orders table that dont have matching ORders in NewOrders...


UPDATE	O
SET	Inserted = 1
FROM Orders O
JOIN NewOrders NeO On O.OrderId = NeO.OrdeRId 
WHERE	MyDate < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), -1)
	AND O.Inserted = 0

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 16:14:17

I think this query is run every day and thus filling the neworders regurlary.
No need to select and update same records over and over again.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 16:14:52

I think the SQL Server 2005 way is more elegant.

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-21 : 16:22:59

quote:
Originally posted by Peso

I think the SQL Server 2005 way is more elegant.

E 12°55'05.25"
N 56°04'39.16"

Yep..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-21 : 16:25:44

quote:
Originally posted by Peso

I think this query is run every day and thus filling the neworders regurlary.
No need to select and update same records over and over again.

E 12°55'05.25"
N 56°04'39.16"

Exactly the reason I was suggesting we join back to the source data to filter out. Your query will mark ALL orders from yesterday as 1.
Now is that an acceptable update depends on a lot of questions, which I believe OP can answer and figure out himself/herself.
I just wanted to provided a diff view too - whether you want to mark ALL orders as 1 or only those that were brought in through the INSERT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-22 : 01:15:39

not run everyday but in batches by date and type (can be 100 a day but they choose to confirm the order by date and type and only those are moved)

I will try these out and test them

thanks all for your help

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-08-22 : 08:04:21

I'm more curious why someone with the obvious experience contained in over 10,000 posts would recommend using a cursor for anything at all.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources