SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Stored Procedure Yes, No		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wldodds
Starting Member

14 Posts
Posted - 09/20/2012 :  13:13:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 09/20/2012 :  13:20:29  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5469 Posts
Posted - 09/20/2012 :  13:24:35  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000