Multiple Updates in Stored Procedure Possible ?

[amurgett]

I have reports that need to update a database table for each row returned. So, say that the report returns 10 rows, the stored proc needs to perform an update for each of those rows returned, using field values returned in the 10 rows (i.e. 10 individual update statements need executing).

Is this sort of thing possible? I can do it with a UFL but the UFL method doesn't work properly with Enterprise where the report will end up.

I am not using any front end language, just Crystal and SQL Server 2000.

Some form of outline code would be really useful.

Thanks in advance.

 

[skuhlman]

Look for CURSORs in your SQL help. CURSORs are used any time you want to loop through data.

Here's just a basic example of looping through a cursor. You'd actually want to make some changes to this to make it useful, but it will give you the general idea:

DECLARE MyCursor CURSOR FOR
SELECT DISTINCT xname, startnum, endnum
FROM MyTableA
OPEN MyCursor

DECLARE @xname CHAR(50)
DECLARE @startnum INTEGER
DECLARE @endnum INTEGER

CREATE TABLE #TempDetail (name CHAR(50), startnum INTEGER, endnum INTEGER)

FETCH NEXT FROM MyCursor
INTO @xname, @startnum, @endnum

WHILE @@FETCH_STATUS = 0
BEGIN
/* other code can go inside this loop */
exec sp_SecondStoredProcedure @startnum, @endnum
FETCH NEXT FROM MyCursor
INTO @xname, @startnum, @endnum
END
CLOSE MyCursor
DEALLOCATE MyCursor
DROP TABLE #TempDetail

 

[amurgett]

Thanks skuhlman,

We had a consultant in today who recommeded the following, however he thought there would be a way of doing the following with a single select statement and possibly using a user defined function. Can you help ?

CREATE PROCEDURE dbo.uspCRYInsertTest
@ReportID INT
AS

-- Drop the the table for testing purposes
DELETE FROM tbl_cry_assessor

--Insert the assessor_id into the table
INSERT INTO tbl_cry_assessor SELECT assessor_id, getdate() FROM tbl_Crystal_HoldingData WHERE Report_ID > @ReportID

--Return the records for the report.
SELECT * FROM tbl_Crystal_HoldingData WHERE Report_ID > @ReportID
GO

 

[synapsevampire]

Your consultant gave you good advice, you should generally avoid cursors wherever possible because of the performance hit.

You might want to use the Truncate Table instead of the Delete command. Truncate results in less logging and overhead than running a DELETE command.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com