use another sp inside a sp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

use another sp inside a sp

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-10-06 : 15:09:38

Hi

I have a stored procedure that use one input parameter, this give me x number of rows where one value could be null, if it is null I would like to retrieve a value from another stored procedure (that needs 2 input parameters), can someone show a example of this?

Note: Since I need 2 parameters I pass in a total of 3 parameters to the original stored procedure.

Best Regards

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 15:20:45

so you mean in the first SP, one of the columns can be NULL? and you want to call another SP recursively to fill in the missing column value for all rows?

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-10-06 : 15:32:01

Hi

Like this... (a fragment of it...)

@PID Int,
@CID Int,
@Lang nVarChar(50)

SELECT TOP (100) PERCENT dbo.Article.ArtNo, dbo.Article.Name, dbo.Cart.OrderName, dbo.Cart.Quantity, ISNULL(dbo.tbl_ProviderName.ProviderID, 0) AS ProviderID,

If the ProviderID ISNULL I set it to 0, but instead I would like to execute another stored procedure (if ISNULL) and pass @CID and @Lang on to that procedure so that this procedure can return a Int value which will be the ProviderID.

Hope this make sense....

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 15:46:49

You can't really do what you are asking. If your second SP was made into a scalar, user defined function it would work but it may not be too efficient. Another alternative is to take the logic from the second SP and put it into a CROSS (or OUTER) APPLY subquery then isNull your original value with the value returned by the APPLY statement.

Be One with the Optimizer
TG

magmo
Aged Yak Warrior

558 Posts

Posted - 2009-10-06 : 15:49:51

I have no experience using subquery's, can you give me an example?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-06 : 15:52:06

yes or may be have a view with all CID, lang, ProviderID and join it within your first SP.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-06 : 16:05:35

quote:
Originally posted by magmo

I have no experience using subquery's, can you give me an example?

Here's a fake example of using a sql statement in a cross apply:


select [id], isNull(st.[a], ca.[a]) as [a]
from   sometable st
cross apply (
       select max(sot.a)
       from someOtherTable sot
       where sot.[id] = st.[id]
       ) ca

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources