CODEWITHSUNDEEP
sql-serverloops
Icerman
Icerman

Reputation: 1119

Pass individual parameter from a set to SQL stored procedure

I have a user defined type as below: 

CREATE TYPE [Integer_udt] AS  TABLE (
    [Id] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC));

Then I have populated it from a query:

DECLARE @Ids [Integer_udt]
    INSERT INTO @Ids
        SELECT table1.Id 
        FROM table1

Next, I need pass individual parameters to a stored procedure which accepts a single Id:

EXEC prc_complicated_calculation @Id

Assuming I can't change prc_complicated_calculation. What is the best way to call it?

Upvotes: 0

Views: 215

Answers (2)

anon
anon

Reputation:

My first answer would be dynamic SQL for simple logic and lines of code.

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'EXEC prc_complicted_calculation ' 
    + RTRIM(Id) + ';'
    FROM @Ids;

EXEC sp_executesql @sql;

You can also do this with a loop, but I think it's more work for little gain (unless you are allergic to dynamic SQL).

DECLARE @i INT;
DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT Id FROM @Ids;

OPEN c;

FETCH NEXT FROM c INTO @i;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC prc_complicated_calculation @i;

    FETCH NEXT FROM c INTO @i;
END

CLOSE c;
DEALLOCATE c;

Upvotes: 3

mikurski
mikurski

Reputation: 1363

In order to use a custom type, the stored procedure that uses it needs to have the type argument set as READONLY.

Since you can't alter the stored procedure, your best bet is probably to follow Aaron's answer on this.

Upvotes: 0

Related Questions