CODEWITHSUNDEEP
sql-server-2005
hmk
hmk

Reputation: 969

SQL Server 2005 stored procedure problem

I am writing a stored procedure like this:

create PROC uspInvCustomerLineItemGetList1(@CustomerID varchar(50))
AS
BEGIN   
    SELECT LineItemID, LineItemName 
    from tblInvoiceLineItems
    where CustomerID = @CustomerID 
       or CustomerID = '' 
       and AccountNumber = (select AccountNumber from tblInvAccountDetails 
                            where AccountTypeID = 10 and 20)

END

My problem is sub query passing 2 or more values (select AccountNumber from tblInvAccountDetails where AccountTypeID = 10 and 20) so I will compare both accountnumbers (1000, 10003, 1006) based on AccountTypeID = 10 and 20

How can I write the stored procedure please?

Thank You

hemanth

Upvotes: 0

Views: 70

Answers (1)

gbn
gbn

Reputation: 432210

Use IN not =

...
where
    CustomerID=@CustomerID
    or
    CustomerID=''
    and
    AccountNumber IN  (select AccountNumber from tblInvAccountDetails where AccountTypeID IN (10, 20))

Note:

  • AccountTypeID can not be both 10 and 20 at the same time: I assume you mean OR. IN is a shorthand for multiple ORs
  • AND has precedence over OR so check if you need ( and ) somewhere

Upvotes: 2

Related Questions