Processing indiviual rows - 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

Transact-SQL (2000)

Processing indiviual rows

Author

Topic

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-08-30 : 04:30:29

Hi, I have been gone for a long time and am just back trying to get back up to speed on things!

I have searched the forum for items on this topic but am either blind or am not entering the right phrase to search for.

My problem is this...

I have TableA with 600 rows.
I want to cycle through each row and use the id from the current row in a string to pass to a stored procedure.
The stored procedure uses the id string to obtain and calculate data
and writes this data to TableB
The results that will be sent to the user will be a simple query output from a join on TableA and TableB.

From what I see it seems to be a lot of work to try to do this in SQL rather than say VB/VB.NET etc.

I looked at cursors which seem to fit the bill but I read alot where people suggest cursors should be avoided?

Could someone point me to a topic/post which may help me please?

Thanks....Hog

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-30 : 05:06:55

Can you be little specific? Can u post some code and expected output?
I think your problem may not be that demanding and you can avoid cursor in this case.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 05:34:46

Something like this?

DECLARE	@CurrentID INT,
	@MaxID INT

SELECT	@CurrentID = MIN(ID),
	@MaxID = MAX(ID)
FROM	MyTable

WHILE @CurrentID <= @MaxID
	BEGIN
		EXEC SomeSP @CurrentID

		SELECT	@CurrentID = MIN(ID)
		FROM	MyTable
		WHERE	ID > @CurrentID
	END

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 05:36:47

Well it's be less work in sql server than vb or vb.net
To call the sp

declare @id int
select @id = 0
while @id < (select max(id) from tbl)
begin
select @id = min(id) from tbl where id > @id
exec mysp @id
end

The problem is that you are calling an sp for each row which will be slow - that is the sort of processing that is more applicable to a procedural language than a set based one. It would be better if you could redefine the process in terms of set based processing - i.e. not talk about processing individual rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-08-30 : 06:05:25

OK, thanks chaps. The code below is how I got the results I wanted by use of a cursor, but am interested in not using it as cursors seem to be not the way to go?

Peso and nr I see your code is similar and will try it out, thnx :)

nr I am confused how I am to make it set based when I need to process each row in its own right?

WHILE @@FETCH_STATUS = 0
BEGIN
SET @WHereClause = ' where asm_no = ' + '%' + @PartNo + '%' + ' and location = ''%WDS%'''
exec dbo.ou_obsolete_mailing_costs_base_sp @WhereClause
FETCH NEXT FROM rt_cursor INTO @PartNo
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 06:08:28

SET based? Probably with a function.

INSERT TableB
SELECT dbo.fnSomeScalarValue(TableA.SomeCol)
FROM TableA

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 06:33:20

Peso
The sp inserts into a table so can't be replaced by a function.

hog
You need to look at what you are doing. If you need to call the sp for each row then you are stuck and it will always be inefficient.
You should look at incorporating the processing in one sp or passing a temp table and processing all rows in single statements.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-08-30 : 07:04:40

nr, I see what you mean. What I was trying to do however was to utilise an already existing sp thus saving on recreating the same code again.

But I see what you mean about trying to get out of the procedural thinking.....something I find very difficult to do :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 08:05:09

quote:
Originally posted by nr
Peso
The sp inserts into a table so can't be replaced by a function.

I can't tell what he wants. He wrote

quote:
Originally posted by hog
I want to cycle through each row and use the id from the current row in a string to pass to a stored procedure.
The stored procedure uses the id string to obtain and calculate data
and writes this data to TableB

Is that an UPDATE? INSERT? I don't think he is very clear about that.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 08:12:43

>> writes this data to TableB
Sounds like a dml statement which isn't allowed in a function - but who knows.

Still would try not to use a function here as it would again serialise row processing - but better than calling an sp for each row.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-30 : 08:15:47

For what I know, the SP processing could be a simple COUNT or SUM for something easy, such as how items are in stock with that ID.

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-30 : 08:42:06

Think we're probably having a serious agreement here :).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hog
Constraint Violating Yak Guru

284 Posts

Posted - 2006-08-31 : 10:45:52

Hi, OK here is the deal, just so you know where I am coming from.

Previously created and in use sps

sp1 lists the contents of packs and is used in its own right.

sp2 calls calls sp1 to get the contents of a pack and then returns sumamary data pertaining to the pack not available from sp1 alone.

The new problem I was trying to resolve was that I now have a table of pack ids which all require sp2 running on them automatically.

So I was trying to cycle through the table calling sp2 for each row to get the summary data and write it to a new table.

This new table would then be a long list of sp2 outputs.

Does that make sense?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources