Stored Procedure Yes, No - 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 2008 Forums

Transact-SQL (2008)

Stored Procedure Yes, No

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2012-09-20 : 13:13:52

I have an appication that contains a stored procedure which evaluates conditions in an 'if else' model, so if a condition is true then the condition is matched and an appropriate error message is thrown.

I'm trying to create a new condition to check for which needs to run a stored procedure within a stored procedure (not sure if this is even supported).

So I set a variable that I can then pass to the sproc and I'm trying to evaluate if a 0 or 1 is returned from the sproc. I can run the sproc just fine without the (exec ...) but it won't run using the below scenario...

Declare
@Coverage_Id int
Set
@Coverage_Id = (select a.Customer_Coverage_Id from pmsiCustomerCoverage a, spv3SalesDocument b
where (((a.[Postal_Code] = b.Zip OR a.[Postal_Code] = '')
AND (a.[State] = b.State OR a.[State] = ''))
AND (a.[Customer_Num] = b.Customer_Num OR a.[Customer_Num] = b.Payerid_1))
and b.Sales_Doc_Num = 'ORDGN00000028201')

if (exec _CustomerExclusion
@Customer_Coverage_Id = @Coverage_Id,@Item_Number='0325803570',@Customer_Num='PT01502751') > 0
then begin @conditionwasmatched

any ideas?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-20 : 13:20:29

As far as I know you cannot do that. I'd also suggest using an OUTPUT parameter instead of checking the return value. But, if you want to check the return value you can do sometihng like:

DECLARE @RetVal INT
EXEC @RetVal = _CustomerExclusion @Customer_Coverage_Id = @Coverage_Id,@Item_Number='0325803570',@Customer_Num='PT01502751'

if @RetVal > 0
begin 
    @conditionwasmatched
END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-09-20 : 13:24:35

The other thing I see is the first statement that sets the value of @coverage_id. Is it guaranteed to return exactly one value? If not then you need to deal with that.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources