nisupport
asked on
How do I execute a stored procedure for multiple records?
I have setup a stored procedure, that works to loop through table records. The code is attached. Is there a better way to perform this action?
ALTER PROCEDURE [dbo].[Batch]
As
Set nocount on
DECLARE @Date smalldatetime, @recordID int, @clientID varchar(13)
SELECT @recordID= Min(recordID)
FROM tbl_BatchProcess
SELECT @clientID = clientID, @Date = date
FROM tbl_BatchProcess
WHERE recordID = @recordID
WHILE @recordID is not null
BEGIN
--execute stored procedure
Execute CalculateSC @Danas, @iZahtjevID, @pJMBG
--get next field
SELECT @recordID= Min(recordID)
FROM tbl_BatchProcess
WHERE recordID> @recordID
SELECT @clientID = clientID, @Date = date
FROM tbl_BatchProcess
WHERE recordID = @recordID
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the stored procedure is "Changing Data", which is implied by the name CalculateSC but does not Return any values directly, then you cannot create a user defined function callable in a Select to do that. A Rule in ANSI compliant databases.
it would be nice to have such feature ;)
If you are already inserting values in a temp table.. can you just change the function to an update statement that changes the data for all the rows in the temp table at once? Can you post the code for that function? That will be better performing than looping or cursors
SELECT clientID, "date", CalculateSC (@Danas, @iZahtjevID, @pJMBG) as fResult
FROM tbl_BatchProcess
WHERE recordID >= @recordID
just I could not get what are those parameters... I guess you need to replace with column names from table tbl_BatchProcess