IF in Storedprocedure within a select, remove sp_executesql @query

Question

I have a Storedprocedure that unfortunately only because of an IF has ceased to be a select and turned everything into a VARCHAR statement and then I run it, losing one of the main benefits of MSSQL which is the compilation.

I modified the SP only to illustrate the problem:

 ALTER PROCEDURE [dbo].[stp_Admin14_listagem_impressao]
        @intacao int,
        @IDCorretor int
    AS
    BEGIN

    declare @query nvarchar(max)

    set @query = 'SELECT  * from exemplo
    WHERE     intacao = '+  cast(@intacao as varchar) +') 

--// #### AQUI é o problema, o que fazer???
    if (@IDCorretor <> 0)
    BEGIN
        set @query  = @query + ' AND tbl_imoveis.int_CORRETOR = ' + cast(@IDCorretor as varchar)
    END


    exec sp_executesql @query
    END

In short, if you see the @Idcorretor <> 0 parameter I should add a Where clause

Answer 1

You can always keep the condition in the query, including a OR for the parameter; this eliminates the IF.

It goes something like this:

...AND (@IDCorretor = 0 OR tbl_imoveis.int_CORRETOR = @IDCorretor)

Then the stored complete trial would look like this:

ALTER PROCEDURE [dbo].[stp_Admin14_listagem_impressao]
    @intacao int,
    @IDCorretor int
AS
BEGIN
    SELECT * from exemplo WHERE intacao = @intacao as varchar
    AND (@IDCorretor = 0 OR tbl_imoveis.int_CORRETOR = @IDCorretor)
END

Note the parentheses I added. Don’t forget them!

A remark

(...) losing one of the main benefits of MSSQL which is the compilation.

You don’t miss any performance benefits by using sp_executesql but rather for concatenating the parameters in the string instead of using them as the actual parameter.

See how to pass your parameters instead of concatenating them in the query: sp_executesql (Transact-SQL).

Thus the query execution plan will be reused in subsequent executions when there has been a change only in the value of the parameters.