March 18, 2008 at 12:13 pm
I'm am attempting to execute one stored procedure from inside another stored procedure and use the data in the second stored procedure.
The first procedure will be reused by other stored procedures. Below is a code sample. It is simplified for example:
-- FIRST STORED PROCEDURE
CREATE PROCEDURE proc_check_table
@param1int,
@param2int
AS
SET NOCOUNT ON
DECLARE @result as bit
BEGIN
SET @result = 0
-- Do some logic and set @result to 1 if nessasary.
SELECT @result AS result
RETURN
END
-- SECOND STORED PROCEDURE
CREATE PROCEDURE proc_edit_data
@param_Aint,
@param_Bint
AS
SET NOCOUNT ON
DECLARE @result AS bit
CREATE TABLE #result (
[result] [bit]
)
BEGIN
-- These do not work but illistrates my intention
INSERT INTO #result
EXEC ('proc_check_table
param1=@param_A,
param2=@param_B')
-- OR --
SET @result = (
proc_check_table
param1=@param_A,
param2=@param_B
)
-- Do some logic with #result or @result so I know the value of the 'result' column from first procedure
END
March 18, 2008 at 12:23 pm
Recommendation would be to add another parameter, and make it an OUTPUT parameter.
Books online has a decent example (Look for Use Output Parameters once on the page):
http://msdn2.microsoft.com/en-us/library/aa258259(SQL.80).aspx
Your mocked up code would look a little like:
-- FIRST STORED PROCEDURE
CREATE PROCEDURE proc_check_table
@param1 int,
@param2 int,
@res bit=0 OUTPUT
AS
SET NOCOUNT ON
DECLARE @result as bit
BEGIN
SET @result = 0
-- Do some logic and set @result to 1 if nessasary.
--SELECT @result AS result
set @res=@result;
RETURN
END
-- SECOND STORED PROCEDURE
CREATE PROCEDURE proc_edit_data
@param_A int,
@param_B int
AS
SET NOCOUNT ON
DECLARE @result AS bit
CREATE TABLE #result (
[result] [bit]
)
BEGIN
declare @result bit
Exec proc_check_table @param1=@param_A,
@param2=@param_B,
@result=@res OUTPUT
-- These do not work but illistrates my intention
INSERT INTO #result
Values @result
-- Do some logic with #result or @result so I know the value of the 'result' column from first procedure
END
Of course - I'd avoid using this in a row-by-row process. SQL does things best in a set based fashion (i.e. NOT one row at a time). Row By Row (or as it's been nicknamed around here, RBAR - Row By Agonizing Row) is a perf killer: avoid it at all costs.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply