Mounting dynamic query with Stored Procedure in SQL Server

Question

Mounting dynamic query with Stored Procedure in SQL Server

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I need to mount a conditional query with a stored procedure, to make my problem clearer, I'll expose the sp here with what I want it to work, look at the end where I use the IFs, that's the problem.

USE [MarcenariaDigital]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PRO

insira o código aqui'insira o código aqui'CEDURE [dbo].[ps_precificacao]
-- Add the parameters for the stored procedure here
@fornecedorId int,
@marcaId int,
@corId int,
@descricao varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
        select pc.preco_id, 
           pc.preco_valor, 
           pc.preco_data_atualizacao, 
           m.material_descricao as material, 
           m.material_id as materialId, 
           c.cor_descricao as cor, 
           ma.marca_descricao as marca,
           m.material_medida as medida,

           um.unidade_medida_descricao as unidade,
           DATEDIFF(day, pc.preco_data_atualizacao, SYSDATETIME()) as dias
    from dbo.tb_precificacao pc
    inner join (Select t.preco_material_id, max(t.preco_id) as maior
    from dbo.tb_precificacao t
    group by t.preco_material_id) tpc
    on tpc.maior = pc.preco_id
    right join dbo.tb_material m
    on m.material_id = pc.preco_material_id
    left join dbo.tb_cor c
    on m.material_cor_id = c.cor_id
    left join dbo.tb_marca ma
    on m.material_marca_id = ma.marca_id
    left join dbo.tb_unidade_medida um
    on m.material_unidade_medida_id = um.unidade_medida_id
    where m.material_inativo = 0
    and pc.preco_fornecedor_id = @fornecedorId
    if @corId > 0
        and (m.material_cor_id = @corId or @corId is null)
    if @marcaId > 0 
        and (m.material_marca_id = @marcaId or @marcaId is null)
    if @descricao is not null
        and (m.material_descricao like '%@descricao%' or @descricao is null)

END

sql-server stored-procedures

asked by anonymous 08.11.2015 / 19:05

2 answers

0

Use the IIF for these quick validations

USE [MarcenariaDigital]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PRO

insira o código aqui'insira o código aqui'CEDURE [dbo].[ps_precificacao]
-- Add the parameters for the stored procedure here
@fornecedorId int,
@marcaId int,
@corId int,
@descricao varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
    select pc.preco_id, 
       pc.preco_valor, 
       pc.preco_data_atualizacao, 
       m.material_descricao as material, 
       m.material_id as materialId, 
       c.cor_descricao as cor, 
       ma.marca_descricao as marca,
       m.material_medida as medida,

       um.unidade_medida_descricao as unidade,
       DATEDIFF(day, pc.preco_data_atualizacao, SYSDATETIME()) as dias
from dbo.tb_precificacao pc
inner join (Select t.preco_material_id, max(t.preco_id) as maior
from dbo.tb_precificacao t
group by t.preco_material_id) tpc
on tpc.maior = pc.preco_id
right join dbo.tb_material m
on m.material_id = pc.preco_material_id
left join dbo.tb_cor c
on m.material_cor_id = c.cor_id
left join dbo.tb_marca ma
on m.material_marca_id = ma.marca_id
left join dbo.tb_unidade_medida um
on m.material_unidade_medida_id = um.unidade_medida_id
where m.material_inativo = 0
and pc.preco_fornecedor_id = @fornecedorId
AND IIF (@corId > 0, (m.material_cor_id = @corId or @corId is null), NULL)
AND IIF (@marcaId > 0, ((m.material_marca_id = @marcaId or @marcaId is null), NULL)
AND IIF (@descricao is not null, (m.material_descricao like '%@descricao%' or @descricao is null), NULL)

29.06.2017 / 18:29

Use variable more times, click event How to execute a function from a url?

score 2 · Accepted Answer

One solution would be:

USE [MarcenariaDigital]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE uspTeste 
    @fornecedorId int,
    @marcaId int,
    @corId int,
    @descricao varchar(50)
AS 

insira o código aqui'insira o código aqui'CEDURE [dbo].[ps_precificacao]
-- Add the parameters for the stored procedure here
@fornecedorId int,
@marcaId int,
@corId int,
@descricao varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
        select pc.preco_id, 
           pc.preco_valor, 
           pc.preco_data_atualizacao, 
           m.material_descricao as material, 
           m.material_id as materialId, 
           c.cor_descricao as cor, 
           ma.marca_descricao as marca,
           m.material_medida as medida,

           um.unidade_medida_descricao as unidade,
           DATEDIFF(day, pc.preco_data_atualizacao, SYSDATETIME()) as dias
    from dbo.tb_precificacao pc
    inner join (
       select t.preco_material_id, max(t.preco_id) as maior
       from dbo.tb_precificacao t
       group by t.preco_material_id
    ) tpc
        on tpc.maior = pc.preco_id
    right join dbo.tb_material m
        on m.material_id = pc.preco_material_id
    left join dbo.tb_cor c
        on m.material_cor_id = c.cor_id
    left join dbo.tb_marca ma
        on m.material_marca_id = ma.marca_id
    left join dbo.tb_unidade_medida um
        on m.material_unidade_medida_id = um.unidade_medida_id
    where m.material_inativo = 0
      and pc.preco_fornecedor_id = @fornecedorId
      and ( m.material_cor_id = @corId or nullif(@corId, 0) is null )
      and ( m.material_marca_id = @marcaId or nullif(@marcaId, 0) is null)
      and ( ( @descricao is not null and m.material_descricao like '%@descricao%' ) or @descricao is null)

END

Edit: If @corID is 0 (or null), @marcaID is 0 (or null) and @description is null, all rows will be returned.