Hello,

I am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?

Thanks. 

USE [MyDB]

GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[stored_proc]
@Col2 varchar(32)
,@Col3 varchar(8)
,@Col4 varchar(15)
,@Col5 int
,@Col6 varchar(32)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Table1]
(Col2,
Col3,
Col4,
Col5,
Col6)
OUTPUT
Inserted.[Col1ID] AS [Col1ID]--Identity column
,Inserted.[Col2] AS [Col2]
,Inserted.[Col3] AS [Col3]
,Inserted.[Col4] AS [Col4]
,Inserted.[Col5] AS [Col5]
,Inserted.[Col6] AS [Col6]
VALUES
(@Col1ID--identity collumn
,@Col2
,@Col3
,@Col4
,@Col5
,@Col6
)
-- RETURN 1;
-- passing parameters from the "OUTPUT"
EXEC [dbo].[calling_stored_proc] @Col1ID=Inserted.[Col1ID], @Col2=Inserted.[Col2], @Col3=Inserted.[Col3]
END

GO

You can use scope_identity() to capture the inserted identity value

....

VALUES
(@Col2
,@Col3
,@Col4
,@Col5
,@Col6
)

declare @ID int
Set @ID = scope_identity()

Use this @ID value for further processing.

Why?  If you pass the identity value of the row that was inserted, why does this second procedure need anything else as a parameter?  Quite simply, you're doing it wrong because you've made it unnecessarily complex. Second, you already have the values inserted into Table1 for columns Col2 and Col3 (assuming there isn't any additional logic executing in a trigger). So, again, why do you need to "retrieve" them in some fashion.  Just pass the arguments of the outer procedure.

Thanks for the reply Scott. What the last proc does is it inserts into another table using the three columns from the OUTPUT. The whole idea is that when clients insert into a table, they see what they have inserted from the OUTPUT, and i wanted to insert into another table by calling the proc at the end of the statement. As you know the identity column is being used in the last proc to pass as a parameter but the scope_identity() returns the last value inserted; and I wanted all the inserted identity column and the other columns to be inserted into the other table by calling the proc at the end of the stored procedure. I do not know if this makes sense. 

Hi Jingyang Li, 

Thank you for the response. But the scope_identity() returns the last value inserted. I wanted all the identity inserted not the last value...

Your calling_stored_proc needs a way to handle multiple values instead of single value parameter.

Search for how to use table-valued parameter for passing table value.

Not sure that I understand why there are two procedures in the first place.

But this article on my web site discusses possible options:
http://www.sommarskog.se/share_data.html

Hi Erland

Thanks for the link, I am a big fan of your website and visit it quite often and thanks for the myriad information you have put out there.

Basically the first proc inserts and the inserted values (from the OUTPUT) needs to be returned to the client for verification. I need to insert some of the values into another table from the OUTPUT/Inserted by invoking the 2nd proc. I do not know if it makes sense but I will see what I can do. 

The syntax inside the second proc is here:

CREATE PROCEDURE [dbo].[calling_stored_proc]
@Col1ID INT
,@Col2 VARCHAR(32)
,@Col3 VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Table3]
([Col1Id],[Cold2],[Col3],[Col4])
VALUES(@Col1ID,@Col2,@Col3,CURRENT_TIMESTAMP)
RETURN 1;
END
GO

• Edited by DayNicha 5 hours 27 minutes ago

Hi Erland

Thanks for the link, I am a big fan of your website and visit it quite often and thanks for the myriad information you have put out there.

Basically the first proc inserts and the inserted values (from the OUTPUT) needs to be returned to the client for verification. I need to insert some of the values into another table from the OUTPUT/Inserted by invoking the 2nd proc. I do not know if it makes sense but I will see what I can do. 

The syntax inside the second proc is here:

CREATE PROCEDURE [dbo].[calling_stored_proc]
@Col1ID INT
,@Col2 VARCHAR(32)
,@Col3 VARCHAR(32)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Table3]
([Col1Id],[Cold2],[Col3],[Col4])
VALUES(@Col1ID,@Col2,@Col3,CURRENT_TIMESTAMP)
RETURN 1;
END
GO

• Edited by DayNicha Friday, May 29, 2015 1:39 AM

Hi,

Try MERGE clause for this.

BOL

MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (UnitMeasureCode, Name)
    VALUES (source.UnitMeasureCode, source.Name)
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

• Edited by Vitaliy Lukashev 43 minutes ago

Hi,

Try MERGE clause for this.

BOL

MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
    INSERT (UnitMeasureCode, Name)
    VALUES (source.UnitMeasureCode, source.Name)
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

• Edited by Vitaliy Lukashev Friday, May 29, 2015 6:23 AM

I still don't understand why you want to call that second procedure. Why not just do:

INSERT firsttable(...)
   OUTPUT ... INTO @result
   VALUES(...)

INSERT secondtable
   SELECT ...
   FROM   @result

It was developed by another developer and he wanted to call the 2nd stored procedure when he could kill two birds with one stone. I was thinking your suggestion initially but I was not really sure and thanks for the suggestion. After the first insert takes place, the client needs to see what is inserted so I will be adding 

INSERT firsttable(...)
   OUTPUT ... INTO @result
   VALUES(...)

SELECT * FROM @result--this will return the values inserted to the client side. 

INSERT secondtable
   SELECT ...
   FROM   @result

To answer your original question.  You need to insert the OUTPUT into a table. In your case you are only inserting 1 row, so this will work.

CREATE PROCEDURE [dbo].[stored_proc]
@Col2 varchar(32)
,@Col3 varchar(8)
,@Col4 varchar(15)
,@Col5 int
,@Col6 varchar(32)
 AS
 BEGIN
SET NOCOUNT ON

DECLARE @outputtable TABLE (Col1ID int);
DECLARE @col1id INT;

INSERT INTO [dbo].[Table1]
(Col2,
Col3,
Col4,
Col5,
Col6)
OUTPUT	Inserted.[Col1ID] AS [Col1ID]--Identity column
	INTO @outputtable
VALUES
(@Col2
,@Col3
,@Col4
,@Col5
,@Col6
)

SET @col1id = (SELECT TOP 1 Col1ID FROM @outputtable)

 -- RETURN 1;
 -- passing parameters from the "OUTPUT"
EXEC [dbo].[calling_stored_proc] @Col1ID=@col1id, @Col2=@Col2, @Col3=@col3
 END

Please see Example A in:

https://msdn.microsoft.com/en-us/library/ms177564.aspx?f=255&MSPPError=-2147217396

Hi DayNicha,

Better practise would be to rewrite the second Stored Procedure(SP) to take a table-valued paramter and pass the result @result.

If there's no way to change the second stored procedure, you may have to use CURSOR to insert the rows in @result to secondtable by calling the second SP. Please see the pseudo-code as below.

INSERT firsttable(...)
   OUTPUT ... INTO @result
   VALUES(...)
SELECT * FROM @result --this will return the values inserted to the client side. 
DECLARE @COL1 nvarchar(50), @COL2 nvarchar(50),
DECLARE your_cursor CURSOR FOR 
SELECT ..
FROM @RESULT
 
OPEN your_cursor
FETCH NEXT FROM your_cursor 
INTO @COL1, @COL2
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC [dbo].[calling_stored_proc] @Col1ID=@col1, @Col2=@col2...
    FETCH NEXT FROM your_cursor 
    INTO @COL1, @COL2
END 
CLOSE your_cursor;
DEALLOCATE your_cursor;

If you have any question, feel free to let me know.