Parameter-based query in SQL Server

Question

Parameter-based query in SQL Server

Navigation

#1 by (2 votes)

0

I have a situation where I need to bring results depending on the parameter reported. If I enter some number, that would be the code, it would only bring the result that has the code. If I did not report anything, it would fetch all the results from the table. In other words, I need to leave the parameter as optional. Here is the code:

create procedure procedure_pedido
    @COD_PEDIDO int = null
as
    begin
        select 
        cod_pedido as PEDIDO, 
        descricao as DESCRICAO      
        from pedido         
        where cod_pedido = @COD_PEDIDO
    end

I wanted to use this parameter condition if possible without if . But I do not know how I would do that.

sql sql-server stored-procedures

asked by anonymous 17.12.2015 / 14:50

1 answer

Could not find or load main class file.java Problem with session Codeigniter error

score 2 · Accepted Answer

You can do this as follows:

Select 
  cod_pedido as PEDIDO, 
  descricao as DESCRICAO      
From pedido         
Where @COD_PEDIDO is Null OR cod_pedido = @COD_PEDIDO

If the parameter is null the condition will be true for all rows in the table.