Stored Procedure Question - 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 Question

Author

Topic

zpnasium

11 Posts

Posted - 2005-01-25 : 03:13:02

I need to write a stored procedure that queries a table and loops through the results. While inside the loop, i need to analyze the data and if necessary, make changes to the row using an update statement to clean up the data in the table.

Can someone point me in the direction of an example of what i am trying to do or a code example that i can play with? I am familiar with SQL, so you can point me to advanced topics/tutorials.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-25 : 04:55:22

use pubs
GO

declare @au_id varchar(30), @phone varchar(30)
set @au_id=''

while 1=1
begin
select top 1 @au_id=au_id, @phone=phone from authors
where au_id>@au_id order by au_id
if @@rowcount=0 break
if @phone like '40%' update table authors set phone=... where au_id=@au_id
else print 'bang!'
end

zpnasium

11 Posts

Posted - 2005-01-25 : 18:57:30

Ok thanks.

So i take it that it is possible to do this...Nevermind the syntax, just the logic. I need to enter into a loop after the initial loop.

#s = SELECT OrderLineNo, OptionsUniqueID FROM stgOrderDetails

while {

#sql = SELECT ExtendedPrice, OptionsUniqueID FROM stgOrderDetails where (OrderLineNo == #s['OrderLineNo']) AND (OptionsUniqueID != '#s['OptionsUniqueID']')
while {
if (#sql['extended price']<0) #sql2 = update stgOrderDetails set ExtendedPrice=0, QtyShipped=0,QtyOptions=0 where OrderID = '#sql['OrderID']';
}

}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:03:39

You should not loop inside a stored procedure unless there isn't a set-based solution. Looping is going to cause performance problems for your query.

Perhaps it would be best to explain what you are trying to do to the rows in the table and why you can't updated them in a set.

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 19:17:19

i need to loop so i can thumb through the data and locate duplicate data in the columns and set them to 0. without looping i can't read the table data.

I have read about the performance issue, but my result set is small (> 3000) so i don't see it being a problem. I have checked and the machine has plenty perf to give for this action.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:20:02

I don't see how finding duplicate data and setting columns to 0 requires looping. Could you provide an example of a table with maybe 10 rows? We'll help you on the UPDATE without a loop.

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 19:25:13

Just look at the logic on my above post, i will work on getting 10 rows.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:29:45

The problem is that I don't understand what you posted in your while logic post, that's why it's best to show what you want with sample data and the expected result.

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 19:38:51

OrderLineNo | ExtendedPrice | OptionsUniqueID
4001962.1 3195 1
4001962.1 3195 2
4001962.1 3195 3
4001962.1 3195 4
4001962.1 3195 5

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:46:19

So you've provided some sample data. Now show us what the data will look like after the update runs and why it would look like that.

Once we have that, we'll be ready to start with the set-based solution.

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 19:49:15

so what i have to do is set ExtendedPrice = 0 if there is more then one instance in the order.

Basicly, only OptionsUniqueID NULL or 1 should have the ExtendedPrice.

OrderLineNo | ExtendedPrice | OptionsUniqueID
4001962.1 3195 1
4001962.1 0 2
4001962.1 0 3
4001962.1 0 4
4001962.1 0 5

zpnasium

11 Posts

Posted - 2005-01-25 : 19:50:07

If the data is correct, it would look like this.

OrderLineNo | ExtendedPrice | OptionsUniqueID
1002621.1 189 <NULL>
1002621.2 1539 <NULL>

So since there is a new OrderLineNo, i don't need to nullify any other data in the order line.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:50:39

So it would look like this:

OrderLineNo | ExtendedPrice | OptionsUniqueID
4001962.1 3195 1
4001962.1 0 2
4001962.1 0 3
4001962.1 0 4
4001962.1 0 5

And how would it look with NULL?

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:53:33

Run this in Query Analyzer. It will not mess up anything that you have as I've coded it with a table variable and not any real tables of yours. Just run it as is the entire thing and view the output. First result set is before image of your table. Second one is the after image.



SET NOCOUNT ON

DECLARE @Table1 table (OrderLineNo int NOT NULL, ExtendedPrice int NOT NULL, OptionsUniqueID int NOT NULL)

INSERT INTO @Table1 VALUES(4001962.1, 3195, 1)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 2)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 3)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 4)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 5)


--before image of the table
SELECT *
FROM @Table1

UPDATE @Table1
SET ExtendedPrice = 0
WHERE OptionsUniqueID <> 1

--after image of the table
SELECT *
FROM @Table1

Does that UPDATE statement do what you want?

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 19:54:36

i think i just figured out who i can update the table with one update statement. let me try.

oops, you just replied.

--------------------

after reading & writing, i think i nailed it down. I guess i just had to break it down enough to see that i could do an update and take care of it in one statement.

UPDATE Table
SET ExtendedPrice = 0
WHERE (OptionsUniqueID <> 1) and (OptionsUniqueID <> NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 19:59:15

You would need OptionsUniqueID IS NOT NULL as you will get unexpected results with = and <> when using NULLs. But you actually don't even need that part to handle the NULLs. Just this would work:

UPDATE Table
SET ExtendedPrice = 0
WHERE OptionsUniqueID <> 1

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 20:01:30

So then why not just do

UPDATE Table
SET ExtendedPrice = 0
WHERE (OptionsUniqueID < 1)

In closing, this would be my entire SP

++++++++++++++++++++++++++++++++++++++

CREATE PROCEDURE sp_CleanData

use DataStore
GO

UPDATE Table
SET ExtendedPrice = 0
WHERE (OptionsUniqueID < 1)

++++++++++++++++++++++++++++++++++++++

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 20:03:49

That won't get the NULL values. But the UPDATE statement that I posted already handles it. Since you can't compare NULL to anything, it is excluded form the OptionsUniqueID <> 1 part already. Run this to see (I added a NULL row for you so that you can see it handles it):



SET NOCOUNT ON

DECLARE @Table1 table (OrderLineNo decimal(8, 1) NOT NULL, ExtendedPrice int NOT NULL, OptionsUniqueID int NULL)

INSERT INTO @Table1 VALUES(4001962.1, 3195, 1)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 2)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 3)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 4)
INSERT INTO @Table1 VALUES(4001962.1, 3195, 5)
INSERT INTO @Table1 VALUES(1002621.1 , 189, NULL)

--before image of the table
SELECT *
FROM @Table1

UPDATE @Table1
SET ExtendedPrice = 0
WHERE OptionsUniqueID <> 1

--after image of the table
SELECT *
FROM @Table1

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 20:05:49

UPDATE @Table1
SET ExtendedPrice = 0
WHERE OptionsUniqueID < 1

That won't work. Use my example that I posted. Replace my UPDATE statement with yours. Notice the after image didn't change the ExtendedPrice to 0.

All you need is:

WHERE OptionsUniqueID <> 1

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-25 : 20:07:42

Oh and some advice:

CREATE PROCEDURE sp_CleanData

use DataStore
GO

Do not change databases inside a stored procedure. To reference objects in another database, use the three part naming convention: DBName.OwnerName.ObjectName.

Also, do not prefix stored procedures with sp_. This causes a performance hit as SQL Server looks in the master database first for this stored procedure, then when it can't find it, it'll look in the user's current database. So use anything but sp_. sp_ is specifically coded for by MS to look in the master database first.

Tara

zpnasium

11 Posts

Posted - 2005-01-25 : 20:08:27

Ok, so now to save the SP for use.
I do not need to look outside the db.
Here is the final results.

++++++++++++++++++++++++++++++++++++++

CREATE PROCEDURE sCleanData
GO
UPDATE TableData
SET ExtendedPrice = 0
WHERE (OptionsUniqueID <> 1)

++++++++++++++++++++++++++++++++++++++

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources