exec sp within sp

[dswitzer]

I'm sure this has been answered many times before on this forum - but I don't seem to be able to find it...

I have an sp(SP#1) built to insert records into a table (Table A).
This sp is used extensively by users and works great.

I am building another sp -- more of a back-office function. If certain conditions are met, I want to add a record into Table A. I would like to just piggyback on SP#1 rather than maintain twice the code.

How can I run SP#1 (and send parameters) from within SP#2. No records need be returned - just pass the parameters and update the table...

Is it as simple as:

create procedure sp2
as
exec dbo.sp1 @param1=1,@param2=2

Thanks.

Dave

 

[gmmastros]

>> Is it as simple as:

Yes

-George

"the screen with the little boxes in the window." - Moron

 

[LNBruno]

If certain conditions are met, I want to add a record into Table A. I would like to just piggyback on SP#1 rather than maintain twice the code.

This sounds more like you'd need a trigger than a nested stored procedure....

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

thast should work

create procedure sp2
as
declare @i int
exec @i =dbo.sp1 @param1=1,@param2=2
if @i <> 0
begin
	print 'something went wrong'
        --put some logging code here

end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

How about to add an additional parameter to sp1 which will show you if sp2 must be runed:

ALTER PROCEDURE sp1(
@param1 ...,
...
@mustsp2run bit = 1)

AS
BEGIN
      ....
      IF (some conditions are met) AND
          @mustsp2run = 1
          sp2
      ....
END

-- Then in sp2
exec dbo.sp1 @param1=1,@param2=2, @mustsp2run = 0

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[dswitzer]

Cool -- I'll dress it up a bit like you guys suggested and do some testing this afternoon.

Thanks.

 

[SQLSister]

I agree with LNBruno, if this is a data integrity issue, you really should put it in a trigger instead. What happens if someone inserts a record using some method other than your sp? Sooner or later someone will do a bulk insert or or insert records manually in Enterprise manager or using QA or even write a differnt sp to do a specialized insert. If the situation is such that when those conditions are ever met no matter how the data got inserted into the table then you must use a trigger to make sure this happens. And if the data involves the value from a prticular field, you also need to account for what happens if the field is ever updated.

Questions about posting. See faq183-874