Call Stored procedure from within another stored procedure, return values
Yazan: esersahin Kasım 24, 2008
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. '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
'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. (
@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
CREATE PROCEDURE sp_ChildStoredProc
