Calling a stored proc that returns a recordset from within a stored proc

Question

Working in SQL Server 2005, I have a stored procedure that inserts a record and returns the new ID via SELECT @@IDENTITY; as the last command.

I then want to call this from another stored proc, and get the value of the new ID.

But I can't work out how to get the value returned from the first procedure.

Example:

CREATE PROCEDURE spMyInsert(@Field1 VARCHAR(10)) AS 
BEGIN

    INSERT INTO tMyTable (Column1) VALUES (@Field1); // ID column implicitly set

    SELECT @@IDENTITY ID;

END


CREATE PROCEDURE spMyMain AS
BEGIN

    DECLARE @NewID INT;

    EXEC spMyInsert 'TEST';

    // How do I set @NewID to the value returned from spMyInsert?

END

There is another question that nearly answers my question, but not quite. This explains how to insert the results into another table, but all I want to do is store it in a local variable.

Looking at other similar questions, the general answer is to change to either set an OUTPUT variable or create a function to do it, but I can't do this in my case as other .NET data access stuff uses the same stored proc, and I don't want to have to duplicate all the work of the stored procs as functions as well.

I couple of things that I've tried but all fail are:

SET @NewID = (EXEC spMyInsert 'TEST');

SET @NewID = (SELECT ID FROM (EXEC spMyInsert 'TEST'));

Anybody know how to do this?

Thanks, Ben

Answer 1

Try this:

Execute @NewID = spMyInsert 'TEST'

Edit: After reading his question more thoroughly and realizing he was dealing with a select rather than a return: Could you wrap that procedure in a function call and then call the function?

select @NewId = from fnMyInsert('TEST')

Answer 2

By the way you should probably check that @@identity is what you need as opposed to scope_identity.

If it is what you need then it will still be accessible in the calling stored procedure too.

CREATE PROCEDURE spMyMain 
AS
BEGIN
    DECLARE @NewID INT;

    EXEC spMyInsert 'TEST';

    SET @NewID = @@IDENTITY

    SELECT @NewID AS '@NewID'

END

The more general solution that would need to be applied if you use scope_identity and don't want to use either output parameters or the procedure return code is

CREATE PROCEDURE spMyMain AS
BEGIN

 DECLARE @NewID INT;

    DECLARE @IdHolder TABLE
    (
    id INT
    )

    INSERT INTO @IdHolder
    EXEC spMyInsert 'TEST';

    IF @@ROWCOUNT<>1
    RAISERROR('Blah',16,1)

     SELECT @NewID = id FROM @IdHolder

END

Answer 3

First, don't use @@IDENTITY, use SCOPE_IDENTITY() instead (search this site or Google for the reason why). Then just return the value in an output parameter:

CREATE PROCEDURE spMyInsert(@Field1 VARCHAR(10), @NewID int output) AS  
BEGIN 
    INSERT INTO tMyTable (Column1) VALUES (@Field1);
    SET @NewID = scope_identity();
END 
 go

CREATE PROCEDURE spMyMain AS 
BEGIN 
     DECLARE @NewID INT; 
     EXEC spMyInsert @Field1 = 'TEST', @NewID = @NewID OUTPUT; 
END
go

Answer 4

The issue here is that the spMyInsert returns a Select. When you execute spMyMain it will return the Select from spMyInsert and then the select from spMyMain

I would suggest that you amend spMyInsert to utilise OUTPUT parameters

CREATE PROCEDURE spMyInsert(@Field1 VARCHAR(10), @NewId int output) AS 
BEGIN

    INSERT INTO tMyTable (Column1) VALUES (@Field1); // ID column implicitly set

    SELECT @NewId = @@SCOPE_IDENTITY;

END

and then

CREATE PROCEDURE spMyMain AS
BEGIN

    DECLARE @NewID INT;
Set @NewId = 0

    EXEC spMyInsert 'TEST', @NewId output;

select @NewId
    // How do I set @NewID to the value returned from spMyInsert?

END

Note that I have also changed @@Identity to @@scope_identity It is better to use @@Scope_Identity as that will return the new ID that applies to the current connection.

Answer 5

An output parameter is the way to go, but if you really can't change the inner SP then, as you say, you can have the inner SP return its results to a table and then get the value out of there.

eg.

declare @NewID int,
@Customer table(CustomerId int);

insert into @Customer
exec spMyInsert 'TEST';

select @NewID = CustomerId from @Customer;