CODEWITHSUNDEEP
sql-servert-sql
user1413
user1413

Reputation: 537

Insert output value from stored procedure to a table

I have created a table storing stored procedure. I have nearly 50 different values to be entered into stored procedure so I have created a table to execute them catching the value from db itself. Below is the process I am using.

DECLARE @spvariable varchar(max);
SELECT @spvariable = e.ExecutionQuery FROM dbo.tmpsptable e where e.ID = @cnt;
Exec @spvariable;

In the stored procedure I have a value which returns after execution of each stored procedure, means there is a select statement at the end of SP with SCOPE_IDENTITY(). Each values are unique of course. Now the question is, for each execution, I wanted to store the returned value in my table tmpsptable. But how do I call that and save on a specific row?

Openrowset is blocked to use it on my server. I cannot add insert statement into that same SP as it is used for other purposes as well.

Below is the one I am trying. I know using OUTPUT parameter is wrong in this case. But what can be the solution??

DECLARE @spvariable VARCHAR(MAX);
DECLARE @id INT;
DECLARE @insertedId TABLE(id INT);
DECLARE @cnt INT= 1;
WHILE @cnt <= 10
    BEGIN
       SELECT @spvariable = e.ExecutionQuery FROM dbo.tmpsptable e where e.ID = @cnt;
       EXEC @spvariable;  
        OUTPUT INSERTED.SerialNumber
        INTO @insertedId;

       UPDATE e
          SET
              SerialNumber = @insertedId
        FROM dbo.tmpsptable e
        WHERE e.ID = @cnt;
        SET @cnt = @cnt + 1;
    END;

Upvotes: 0

Views: 85

Answers (2)

RBarryYoung
RBarryYoung

Reputation: 56725

If I understand what you are trying to do, the you want to use the INSERT..EXEC syntax. There are a number of limitations, but it should probably work for you:

DECLARE @spvariable VARCHAR(MAX);
DECLARE @id INT;
--DECLARE @insertedId TABLE(id INT);
-- you cannot INSERT from an EXEC into a @table variable
CREATE TABLE #insertedId(id INT);

DECLARE @cnt INT= 1;
WHILE @cnt <= 10
    BEGIN
       SELECT @spvariable = e.ExecutionQuery FROM dbo.tmpsptable e where e.ID = @cnt;

       DELETE FROM #instertedId;
       -- NOTE: the EXEC Result Set must exactly match the column types
       INSERT INTO #insertedId
       EXEC @spvariable;

       UPDATE e
          SET
              SerialNumber = (Select TOP 1 id From #insertedId)
        FROM dbo.tmpsptable e
        WHERE e.ID = @cnt;
        SET @cnt = @cnt + 1;
    END;

Upvotes: 1

IVNSTN
IVNSTN

Reputation: 9299

I know using OUTPUT parameter is wrong - why?

If you are using SCOPE_IDENTITY() then this is always a single row created per SP call, then you surely may return this scalar value as output parameter:

EXEC @spvariable
  @newID = @newID output

but in general I don't like your approach. WHILE, single-row SPs...

Upvotes: 0

Related Questions