call one stored procedure within another 1

[samantha72]

Can you call one stored procedure within another and if so what is the
syntax for calling it.

Thanks
Samantha

 

[Tim1]

Hiya Samantha,

You certainly can call one stored procedure from another, and the syntax is just the same as running it any other time:

EXEC stored_proc_name

Tim

 

[Guest_imported]

yes, if you are calling another stored procedure in any line which is not first line then you have to put exec


eg

1>sp_who
2>go

but
1>declare @int int
2>exec sp_who
3>go

 

[smonet]

can you call an SP in a regular select statement?
for eg. if sp_testc is a stored proc then is it possible (in some way):

select @a = exec sp_testc
go


thanks
steve

 

[KarthikBabu]

Steve, i think your requirement is to capture the value returned by the SP sp_testc into the variable @a. If so, please try

exec @a = sp_testc
go

Hope this solves your problem.

 

[smonet]

thanks karthikbabu.
that works if i have one value to capture.
actually what i am looking for is to write custom functions in SQL which can then be used in a regular query. as far as i know that is not possible but i thought that there may be some workarounds.
steve