Procedure price range

Question

Procedure price range

Navigation

#1 by (1 votes)

0

I'm trying to create a procedure that, depending on the price range, takes a different field from the table.

Create Procedure BuscaPreco
@preco decimal,
@faixa1 int,
@faixa2 int,
@faixa3 int,
@faixa1=100,
@faixa2=200,
@faixa3=300
Select 
case when @preco <@faixa1 then p.preco1
when @preco <@faixa2 and @preco >=@faixa1 then p.preco2
when @preco <@faixa3 and @preco >= @faixa2 then p.preco3
else p.preco4
end,
from produtos p

Msg 102, Level 15, State 1, Procedure BuscaPreco, Line 6
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Procedure BuscaPreco, Line 15
Incorrect syntax near the keyword 'from'.

sql sql-server stored-procedures

asked by anonymous 24.10.2017 / 16:15

1 answer

Decreasing the value of two fields of a table and sorting according to the result Show "Loading ..." during page navigation

score 1 · Accepted Answer

You are declaring more than once the same variables instead of assigning a default value and there is also a "," after your case.

CREATE PROCEDURE BuscaPreco
    @preco DECIMAL,
    @faixa1 INT = 100,
    @faixa2 INT = 200,
    @faixa3 INT = 300
AS
SELECT
    CASE
        WHEN (@preco < @faixa1) THEN p.preco1
        WHEN (@preco < @faixa2 AND @preco >= @faixa1) THEN p.preco2
        WHEN (@preco < @faixa3 AND @preco >= @faixa2) THEN p.preco3
        ELSE p.preco4
    END
FROM produtos p
GO