I would like to execute a stored procedure over each row in a set without using a cursor with something like this:
SELECT EXEC dbo.Sproc @Param1 = Table1.id
FROM Table1
I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I'd like to use a stored procedure if possible (company standards)
Cursors are particularly useful in stored procedures. They allow you to use only one query to accomplish a task that would otherwise require several queries. However, all cursor operations must execute within a single procedure.
Stored procedures are pre-compiled objects and executes as bulk of statements, whereas cursors are used to execute row by row.
Cursors could be used in some applications for serialized operations as shown in example above, but generally they should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data.
You cannot reference a cursor declared in a procedure from another procedure. But when you declare a cursor in the package, you can call it from other procedures located in the same package, in other packages, and even from standalone procedures.
Yes. If you have a column you can use within the table to mark the ones processed, you can use WHILE EXISTS:
DECLARE @Id int
WHILE EXISTS(SELECT * FROM Table1 WHERE Processed='N')
BEGIN
SELECT Top 1 @Id = id from Table1 WHERE Procesed='N'
EXEC dbo.Sproc @Id
UPDATE Table1 SET Processed = 'Y' WHERE Id = @Id
END
Alternately, dump the ids into a temp table or table variable and delete when finished:
DECLARE @HoldTable table (Id int PRIMARY KEY)
DECLARE @Id int
INSERT INTO @HoldTable SELECT Id FROM Table1
WHILE EXISTS(SELECT * FROM @HoldTable)
BEGIN
SELECT @Id = id from @HoldTable
EXEC dbo.Sproc @Id
DELETE FROM @HoldTable where Id = @Id
END
If you're only using SQL Server 2005 or newer, don't care about backwards compatibility and can convert your code to be in a User-Defined Function (rather than a stored proc) then you can use the new "CROSS APPLY" operator, which does use a syntax very similar to what you want. I found here a short intro (of course, you can also read the BOLs and MSDN)
Supposing your SP returns a single value named out_int, your example could be rewritten as:
SELECT T.id, UDF.out_int
FROM
Table1 T
CROSS APPLY
dbo.fn_My_UDF(T.id) AS UDF
This will retrieve each "id" from Table1 and use it to call fn_My_UDF, the result of which will appear in the final result-set besides the original parameter.
A variat of "CROSS APPLY" is "OUTER APPLY". They are equivalents of "INNER JOIN" and "LEFT JOIN", but work on joining a table and a UDF (and calling the second at the same time).
If you must (by explicit order of the pointy-haired boss) use SPs insead, well - bad luck! You'll have to keep with cursors, or try cheating a bit: change the code into UDFs, and create wrapper SPs :D.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With