Using IF/ELSE for conditioning WHERE

Question

I'm trying to run this sp but... I cannot figure out where is the mistake. can you help me?

alter procedure prova @condizione=1, @parametro=39
    @condizione1 int,
    @parametro int
as

begin
declare @condizione int
set @condizione = @condizione1


    SET NOCOUNT ON
select  pf.CostumerCode, pf.BelongTo
from    tbprodfin pf
    where 
    CASE @condizione
        WHEN 1 THEN pf.IdPF
        WHEN 2 THEN pf.BelongTo
    END  = @parametro

end

Explain what you are trying to achieve. Your Query looks fine — Pரதீப், Dec 01 '14 at 08:25
Are you getting error like Incorrect syntax near **alter procedure prova** — Rajesh, Dec 01 '14 at 11:00

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

Given the little information provided: i suppose to change your select clause to sth. like

select  pf.CostumerCode, pf.BelongTo
from    tbprodfin pf
where 1 = 1
    and (   (@condizione = 1 and @parametro = pf.IdPF)
        or  (@condizione = 2 and @parametro = pf.BelongTo))

This might not work initially but it should get you startet as i hope. Also, see this related question.

edited May 23 '17 at 12:27

Community

1
1

answered Dec 01 '14 at 07:51

nozzleman

9,529
4
37
58

Dudi Konfino · Answer 2 · 2014-12-01T13:58:49.053

0

try it like that

  CREATE procedure prova @condizione int =1,
 @parametro int =39,
    @condizione1 int    --what for??
as
begin
SET NOCOUNT ON
if @condizione=1
begin
select  pf.CostumerCode, pf.BelongTo
from    tbprodfin pf
where pf.IdPF= @parametro
end 
else 
begin
if @condizione=2
begin
select  pf.CostumerCode, pf.BelongTo
from    tbprodfin pf
where pf.BelongTo= @parametro
end  
end
end

edited Dec 01 '14 at 13:58

answered Dec 01 '14 at 08:53

Dudi Konfino

1,126
2
13
24

score 0 · Answer 3 · answered Dec 01 '14 at 11:37

0

If i understand what are u want. There is result with use CASE ...

select  pf.CostumerCode, pf.BelongTo
from    tbprodfin pf
    where 
    (CASE WHEN @condizione = 1 THEN pf.IdPF
          WHEN @condizione = 2 THEN pf.BelongTo END) = @parametro

answered Dec 01 '14 at 11:37

Matej Hlavaj

900
7
10

I think the CASA solution is the one which I would use, otherwise I had to repeat so many IF how much condition I have. – Gianluca Testa Jan 09 '15 at 06:09

Using IF/ELSE for conditioning WHERE

3 Answers3