Calling stored procedure from another stored procedure
-
Is it possible to call a stored procedure inside another stored procedure?
What is the syntax?
-
create proc1
as ...
create proc2 ..
as
exec proc1 --- Inner call
...
* Noel -
Noel gave you the essence. One additional feature I am using more and more lately is the Return Code. Here is an example...
create procedure proc1 as
-- do some calculation
-- if successful, end with
return (0)
-- if it failed, end with
return( [some other number] )
go
create procedure proc2 as
exec @rc = proc1 -- inner call
IF @rc <> 0 begin
-- do something about the error
end
go
-
My procedure also has parameters. So from inside the other stored procedure, if I want the value it returns, I would type:
SELECT ( exec MyProc )
What about the parameters?
-
To get parms back from a sproc, requires the OUTPUT keyword, which must be in the proc declaration, as well as the execution. Like so...
create procedure Proc1 @parm1 varchar(10) = null OUTPUT as
-- blah blah blah
set @Parm1 = 'Hello World.'
go
create procedure Proc2 as
declare @parm varchar(10)
set @parm = 'Overridden'
exec Proc1 @Parm OUTPUT
print @Parm
go
exec Proc2
go
[Results printed]
Hello World.
-
I need the procedure as part of a select statement in another procedure. I tested this and it worked (@LASTNAME is a parameter of otherproc).
This is the FULL stored proc:
CREATE PROCEDURE dbo.p_testproc AS
exec otherproc @LASTNAME='Test'
GO
That worked, but this did not work:
CREATE PROCEDURE dbo.p_testproc AS
SELECT (exec otherproc @LASTNAME='Test')
GO
-
In BOL lookup "output parameters" in the Index. It is explained very clearly there, how to use Return Codes, input and output parameters.
One thing it also states there is:
"When a stored procedure is executed, input parameters can either have their value set to a constant or use the value of a variable. Output parameters and return codes must return their values into a variable. "
So your suggested syntax will not work.
nano
-
If you want to use it this way (inside a SELECT statement), you have to create a UDF (user defined function), not a procedure. However, functions have some limitations, so I can't tell whether it will work for your particular problem.
You can then call the function as SELECT dbo.my_function (@parameter). "Normal" UDF will return only 1 value; if you need to return a recordset, look for TABLE VALUED functions in BOL.
Quotation from BOL : A user-defined function that returns a table can also replace stored procedures that return a single result set. The table returned by a user-defined function can be referenced in the FROM clause of a Transact-SQL statement, but stored procedures that return result sets cannot.
HTH, Vladan
-
Actually it'll be select * from dbo.MyFunction (@Param1,...) if you need to select from it.
-
Well... to be even more precise, what Remi wrote is a select from table-valued function. What I wrote before was meant as a call of scalar function
Of course, you can also use the scalar function in a normal select, which will then look like this:
SELECT col1, col2, col3, dbo.MyFunction(col4)
FROM TableA
or join to the table valued function (example from BOL)
SELECT * FROM tb_Employees AS E
INNER JOIN dbo.fn_EmployeesInDept('shipping') AS EID
ON E.EmployeeID = EID.EmployeeID
-
Thanks, I'll try the UDF. Can someone post a sample UDF so I can see the correct syntax?
-
CREATE FUNCTION [dbo].[fnvwFacturation_ALL] (@BT as bit = 0, @ps as bit = 0, @TEBT as bit = 0, @TETBL as bit = 0, @DateLimite as datetime, @NoPS AS INT)
RETURNS TABLE AS
RETURN
Select TOP 100 PERCENT dtFactures.QryTransfer, dtFactures.Nom, dtFactures.[N° Bon de travail], dtFactures.Date, FFCOMM FROM
(
SELECT QryTransfer, Nom, [N° bon de travail], Date, FFCOMM
FROM dbo.fnvwFacturation_BT(@DateLimite) WHERE @BT = 1
UNION ALL
SELECT QryTransfer, Nom, [N° bon de travail], Date, FFCOMM
FROM dbo.fnvwFacturation_PS(@NoPS) WHERE @ps = 1
UNION ALL
SELECT QryTransfer, Nom, [N° bon de travail], Date, FFCOMM
FROM dbo.fnvwFacturation_TEBT() WHERE @TEBT = 1
UNION ALL
SELECT QryTransfer, Nom, [N° bon de travail], Date, FFCOMM
FROM dbo.fnvwFacturation_TETBL() WHERE @TETBL = 1
) dtFactures
ORDER BY dtFactures.Nom, dtFactures.[N° Bon de travail]
-
You can even use a recordset returned by one sp in another sp, as shown below. This is useful when you have some common select logic you don't want to maintain in multiple places.
... insert into #Temp exec ap_GetDailyUsage @UsageDate ...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply