Call Stored procedure from within another stored procedure, return values

This quick how-to shows an example of having two stored procedures that one stored procedure executes the other stored procedure passing values and returning a value to the calling parent stored proc. This would be handy passing in a value to dynamically populate a lookup table and stored that Id as a foreign key in a datatable. . This example asssumes you would have a parent table and a child/lookup table related back to the parent table. [code] 'This stored procedure is called by a bu ...

This quick how-to shows an example of having two stored procedures that one stored procedure executes the other stored procedure passing values and returning a value to the calling parent stored proc. This would be handy passing in a value to dynamically populate a lookup table and stored that Id as a foreign key in a datatable. . This example asssumes you would have a parent table and a child/lookup table related back to the parent table. [code] 'This stored procedure is called by a business component or webpage 'passing into two parameters, executes sp_ChildStoredProc and passes on value. 'The @Id OUTPUT returns the value. CREATE PROCEDURE sp_ParentStoredProc ( @col1 varchar(20) @ValuePassed varchar(50), ) AS declare @Id int EXEC sp_ReturnValue @ValuePassed, @Id OUTPUT INSERT INTO SomeTable (col1, col2) VALUES (@col1, @Id) RETURN GO [/code] 'This stored procedure is executed in the sp_ParentStoredProc above and passes 'a varchar variable piece of data. The spChildStoredProc will insert this piece of data 'into a lookup table if it doesn't exist, return the Identity column using Scope_Identity() function or 'will return the identity value to the parent stored proc if the piece of data passed in already exists in the lookup table. [code] CREATE PROCEDURE sp_ChildStoredProc ( @ValuePassedIn varchar(50), @Id smallint OUTPUT ) AS If exists (SELECT column1 FROM LookUpTable WHERE column1=@ValuePassedIn) BEGIN SET @Id = (SELECT column1 FROM LookUpTable WHERE column1=@ValuePassedIn) END ELSE BEGIN INSERT INTO LookUpTable(column1) VALUES(@ValuePassedIn) SET @Id = Scope_Identity() END GO [/code]

3 thoughts on “Call Stored procedure from within another stored procedure, return values

  1. CREATE PROCEDURE procedurename1//first sp

    (
    @Number varchar(100)

    )

    BEGIN
    select No,Name,Mark,Results from tablename where Number=@Number

    END
    GO

    CREATE PROCEDURE procedurename2//second sp

    (
    @Number varchar(100)

    )
    AS
    Declare Table @tempteble(@Number varchar(100),Name varchar(100),Mark varchar(100),Results varchar(1))

    BEGIN
    insert into @tempteble(Number,Name ,Mark ,Results)
    Exec procedurename1 @Number

    END
    GO

  2. It is confusing that “sp_ParentStoredSproc” executes “sp_ReturnValue” instead of “sp_ChildStoredSproc”.

[gp-comments width="770" linklove="off" ]