list/array processing in sql server 2000

Remember, this is just another technique. The other techniques have their own pros and cons depending on how they are implemented, and the scenarios they are designed to solve. But most of the other techniques I know never discuss how you can do such processing via an application which connects to the database. But the idea ain't something new.

This approach involves creating two things: an extra table where you need to store the unique ids of items (may be order id, or user id, etc) your are trying to process. And a stored procedure to do your lengthy data retrieval. By lengthy data retrieval I mean the sql query used to fetch a final report/data may be huge that you'd find it cumbersome to include it in your application's code.

The trick is in utilizing your connection's unique id. Observe the below t-sql statements:


CREATE TABLE TrainerList(
TrainerID int,
SPID int
);

GO

INSERT INTO TrainerList (SPID, TrainerID)
SELECT @@SPID, '12345' UNION
SELECT @@SPID, '14523' UNION
SELECT @@SPID, '25364';

GO

CREATE PROCEDURE USP_GET_TRAINER_SESSIONS
AS
BEGIN
DECLARE @TRAINERS (TrainerID int);

INSERT INTO @TRAINERS
SELECT TrainerID FROM TrainerList WHERE SPID = @@SPID;

/* Doing a simple retrieval operation */

SELECT * FROM TrainerSessions
WHERE TrainerID IN (SELECT TrainerID from @TRAINERS);

DELETE FROM TrainerList WHERE SPID = @@SPID;
END

GO

EXEC USP_GET_TRAINER_SESSIONS;


We make use of @@SPID variable. In the application we generate an insert statement like the one I've shown above and execute it. Then we execute the stored procedure. @@SPID value will be unique for a connection. We simply execute the insert statements and then the stored procedure in the same connection.

This might not help in every scenario. If you have a lot of items to process, and you want to avoid passing csv as argument to your stored procedures this is a viable alternative.