output and procedure

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

May 28th, 2015 7:44pm

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.

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 8:22pm

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.
May 28th, 2015 8:29pm

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. 
Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 9:27pm

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...

May 28th, 2015 9:36pm

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.

Free Windows Admin Tool Kit Click here and download it now
May 28th, 2015 9:46pm

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

May 28th, 2015 10:16pm

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
Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 1:15am

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;


May 29th, 2015 6:21am

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

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 7:18am

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

May 29th, 2015 11:51am

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

Free Windows Admin Tool Kit Click here and download it now
May 29th, 2015 12:41pm

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.

May 31st, 2015 1:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics