Is it possible to execute a stored procedure over a set without using a cursor?

Question

I'm using SQL Server and have a table ProjectAccess which I'm hoping to use as a way to "lock" access to Projects. Currently the table looks like this:

CREATE TABLE [dbo].[ProjectAccess] 
(
    [ProjectAccessID] INT IDENTITY (1, 1) NOT NULL,
    [ProjectID] INT NOT NULL,
    [UserSessionID] INT NOT NULL,
    [Locked] BIT NOT NULL,

    CONSTRAINT [uq_UserSessionID] 
        UNIQUE NONCLUSTERED ([UserSessionID]),
    CONSTRAINT [fk_ProjectAccessToProject] 
        FOREIGN KEY ([ProjectID]) REFERENCES [dbo].[Project] ([ProjectID]) 
                ON DELETE CASCADE,
    CONSTRAINT [fk_ProjectAccessToUserSession] 
        FOREIGN KEY ([UserSessionID]) REFERENCES [dbo].[UserSession] ([UserSessionID]) 
                ON DELETE CASCADE, 
    CONSTRAINT [PK_ProjectAccess] 
        PRIMARY KEY ([ProjectAccessID])
);

The idea was to only allow for multiple of the same project ID's into this table, but only one could have the Locked column value as true. For example you could have two records with ProjectID 1 in the table at the same time as long as both did not have the Locked value set to true. So is there a way to do this? I see that there is a check constraint but that seems to check all values in a column satisfy a certain condition which is a little different than what I'm hoping for. Thank you in advance and let me know if anything isn't clear enough.

Solution 1

9 out of 10 times you can do what you want without a cursor or a while loop. However, if you must use one, I have found that while loops tend to be faster.

Also if you do not want to delete or update the table, you can use something like this:

DECLARE @id [type]
SELECT @id = MIN([id]) FROM [table]
WHILE @id IS NOT NULL
BEGIN
    EXEC [sproc] @id
    SELECT @id = MIN([id]) FROM [table] WHERE [id] > @id
END

Solution 2

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

Solution 3

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.

Solution 4

Frankly if I needed to execute a stored proc for a set of data instead if the one record it was written for , I would write the set-based code instead of using the stored proc. This is the only efficient way to do this if you are going to be running against a large data set. You could go from hours to do the insert that the stored proc does to seconds or even milliseconds. Do not use record based processing ever when you need to processs a set of data especially not for a silly reason like reuse of code. Performance trumps reuse of code.

Solution 5

If possible I'd write a second version of the stored proc that reads from a temp table.

If that's not possible than you're probably out of luck.

View more solutions

Comments

[m4gik]

Whoever downvoted please explain why instead of being useless.

[Joe Pineda]

There are some items where you absolutely must use cursors, though as with GOTOs the real need for them is really really small. Agree it's better to do set-based processing wherever it makes sense, but your "performance trumps reuse of code" is a bit extremist.

[dburges]

Since we are talking the differnce between minutes and milliseconds or hours and seconds (or minutes), I don't think it is extremist. Programmers who don't consider database performance over code reuse (particularly when it comes to using cursors) generally have very badly performing databases.

[Richard Collette]

Is this entirely accurate? Can you order by id without specifying a group by clause?

[stannius]

You are the one who said it was seconds vs. hours. In that strawman case who could argue? But if the difference is milliseconds vs. seconds or milliseconds vs. twice as many milliseconds, it depends.

[dburges]

It's hardly a strawman case, it is real life experience. I have fixed cursors that had that kind of improvement, especially on large data set inserts.

[Tony_Henrich]

It doesn't work right. It doesn't stop after reading the max id.

[Walter Stabosz]

-1 Doesn't work. As Richard Collette said, the select syntax is broken. And if you fix the syntax, the while gets stuck in an infinite loop, as pointed out by Tony_Henrich). I've added an answer that has a working loop, but was slow in my experience.

[eidylon]

The problem with pulling the code out of the sproc and into inline set code, is that you may have multiple statements that you want to run for each row, and that you need to call from several different triggers. That's the whole point of encapsulation, is to reduce the amount of code, and centralize it for when you need to make changes to it.

[dburges]

Encapsulation is not a good thing in database terms when it affects performance. It is way down the list of things you should care about in datbase developement. It is great for object-oriented code but does not work well in a database environment when it causes severe performance problems. You can re-write the proc to handle sets of data if you prefer.

[Fandango68]

CROSS APPLY does not work for Stored Procedures, as they do not return datasets, just report outputs. Functions return anything, so that's why SQL can only use them when using CROSS APPLY. Down voted.

[Joe Pineda]

@Fernando68 I indicated 3 necessary conditions for using CROSS APPLY as a solution at the very beginning of my answer, 3rd of which is "can convert your code to be a UDF rather than SP"

[kralco626]

Edited your second answer. You were selecting into @Id instead of @HoldTable and you had a where clause of where processed='N' but @HoldTable has no such column. The second option should now work. I'm hoping I understood your intention correctly.

[Marc K]

Put the exec at the top of the loop, then no need to check the condition twice (obviously you would need to select the id once before the loop).

[evictednoise]

This could only work for specific scenario (auto incremented integer id)

[Abubakar Riaz]

how to use cross apply with stored procedure, since UDF cannot have exec command in its definition.