Parameter-based query in SQL Server

Question

I have a situation where I need to bring results depending on the parameter reported. If I gave you any number, which would be the code, it would only bring the result that has the code. If I didn’t report anything, it would bring all the results from the table. In other words, I need to make the parameter optional. Follow 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 don’t know how I’d do it.

Answer 1

You can do it this way:

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 of the table.