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