Executing Stored Proc within Stored Proc 1

[tonioJ]

Is it possible to execute a stored procedure and return a value within a stored procedure? If so, how will I do it?

Thank you in advance for the reply.

 

[hmckillop]

If its just one value from the stored procedure you need returned you can do something like this.

CREATE proc usp_aProc1
(
	@po_Outputparam int output
)
as
Set @po_Outputparam =11  --change this to be type and value you require


create proc usp_aProc2
as
declare @returnValue int
--call the first procedure with a parameter returned containing the value required
exec usp_aProc1 @po_Outputparam = @returnValue out
select @returnValue

"I'm living so far beyond my income that we may almost be said to be living apart

 

[tonioJ]

how about if i have to return a multiple records with multiple columns??

 

[dbomrrsm]

you can do this by returning the result set to a temporary table - depending on how long you need the results to be available you can use either a local or global temp table by prefixing the table name with # or ## - see temporary tables in BOL for more details.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[hmckillop]

Different question.
If you need to return multiple rows, the easiest way is to declare a function returning a table e.g.

create function ufn_aTest()
returns table 
as
return select [Name] from sysobjects where xtype = 'U'



CREATE procedure usp_aProc
as

select * from dbo.ufn_aTest()

"I'm living so far beyond my income that we may almost be said to be living apart

 

[tonioJ]

I have the code below... but it says: Incorrect syntax near 'FUNCTION' when creating a function. What seem to be wrong with the statement???

CREATE FUNCTION fn__k_payroll_compute_payroll (@co_code varchar(15),
@payroll_period varchar(20) )
returns @retValue table (co_code varchar(15) not null,
emp_code varchar(20),
descriptn varchar(50),
debit numeric(9,4),
credit numeric(9,4))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
Begin
[My Codes]
End

 

[tonioJ]

by the way, I am using SQL SERVER 7.0

thank you for all the reply...

problem still not solved

 

[tonioJ]

i figure out the problem on syntax error. it will only run in sql server 2000 not in sql server 7. thanks for all your replies.