CODEWITHSUNDEEP
sql-server
Dov_l
Dov_l

Reputation: 15

How to retrieve records using parameters?

In my table I have several docNums series.

I want to retrieve conditionally either all series or excluding one series from the set.

I tried the following query which returned error

declare @p char(1) = 1

select 
    t0.docNum  
from   
    OINV t0
where  
    t0.DocNum = case
                   when @p  <> '1' then t0.DocNum                             
                   else (select t1.DocNum
                         from oinv t1
                         where SUBSTRING(convert(varchar, t1.docNum), 1, 1) <> '9' 
                           and LEN(convert(varchar, t1.docNum)) < 7)
                end

What should be the correct query?

Upvotes: 0

Views: 39

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

Pretty sure you were getting an error message about a subquery returning more than 1 row. That is because your subquery would return all the rows from the table. You would need to correlate that subquery to the outer query to avoid this. But you don't need to use a subquery here at all. This can be simplified to something like this.

declare  @p  char(1) = '1'

select o.docNum  
from OINV o
where @p  <> '1'
OR 
(
    SUBSTRING(convert(varchar(10/*whatever size is appropriate*/), o.docNum), 1, 1) <> '9' 
    and 
    LEN(convert(varchar(10/*whatever size is appropriate*/), o.docNum)  ) < 7
)

Upvotes: 1

Related Questions