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 2000 Forums  SQL Server Development (2000)  Sub Queries inside stored procedures

Author  Topic  Robin Starting Member 9 Posts Posted - 2001-10-24 : 16:54:58 Hello, I'm looking for some help. when I run the code below: (Select Flat_Fee From Billing_Exceptions (NOLOCK) Where Billing_Exceptions.Property_ID Like @PropID And Billing_Exceptions.GDS_Code Like LTrim(Billing_Table.GDS_Code)) Is Not Null I'm expecting the statement to return a null if there are no records or a value if it actually found a record. Is it valid to expect this? I'm trying to produce an billing procedure which produces a transaction based invoice. How each transaction is billed depends on the customer's agreement and could include a number of exceptions based on differnet fields in the transaction table. I need to check for relevant exceptions for each and every record, for each and every possible exception type, one at a time as they are being processed. The above statement checks only one of the possible exceptions that are all logged into the exceptions table. It is part of a larger 'Case' statement, but, all checks are ignored when I run the stored procedure and the default is always used even if the above statement returns a valid record. why? the Complete Case Statement: 'VIPFee' = Case When (Select Flat_Fee From Billing_Exceptions (NOLOCK) Where Billing_Exceptions.Property_ID Like @PropID And Billing_Exceptions.GDS_Code Like LTrim(Billing_Table.GDS_Code)) Is Not Null Then Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End When (Select Flat_Fee From Billing_Exceptions (NOLOCK) Where Billing_Exceptions.Property_ID Like @PropID And Billing_Exceptions.GDS_Code Like LTrim(Billing_Table.GDS_Code)) Is Null Then Case When (Select Flat_Fee From Billing_Exceptions Where Property_ID Like @PropID And Billing_Exceptions.Int_Ref_Code Like Billing_Table.Int_Ref_Code) Is Not Null Then Case When Billing_Exceptions.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Billing_Exceptions.Percentage_Fee End Else Billing_Exceptions.Flat_Fee End Else Case When Basic_Invoice_Data.Flat_Fee = 0 Then Case When Rates_Currency Not Like Bill_Currency Then (([Num_Units]*[Daily_Rate]*[Rental_Period]) * Basic_Invoice_Data.Percentage_Fee) * Exchange_Rate Else ([Num_Units]*[Daily_Rate]*[Rental_Period]) * Basic_Invoice_Data.Percentage_Fee End Else Basic_Invoice_Data.Flat_Fee End End End Robin Olsen