Mount Stored Procedure Dynamic Query in SQL Server

Asked

Viewed 907 times

2

need to mount a conditional query with stored Procedure, to make clear my problem I will expose here to sp with what I want to work, note the end where I use Ifs, this there 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

2 answers

2


A 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 @Descricao is null, all lines will be returned.

  • how you did it will fetch the line using the parameter I passed, but if you pass 0 I do not want to return only the lines with null value, but yes, all, understood ?

  • I changed the answer. If corID or marcaID is 0 it will return all lines. You can check if this is what you want?

  • 1

    Success, thank you.

0

Use 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)

Browser other questions tagged sql-server stored-procedures

You are not signed in. Login or sign up in order to post.