IF in StoredProcedure within a select, remove sp_executesql @query

Question

IF in StoredProcedure within a select, remove sp_executesql @query

Navigation

#1 by (1 votes)

2

I have a StoredProcedure that unfortunately only because of an IF it stopped being a select and turned everything into a VARCHAR statement and then I execute it, losing one of the main benefits of MSSQL being compiled.

I modified SP just 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 the @IDCorretor ; 0 should I add a clause where

sql sql-server tsql

asked by anonymous 16.01.2015 / 12:24

1 answer

Web Forms for MVC 5 How to perform a function only when the page is printed?

score 1 · Accepted Answer

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

It looks something like this:

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

So the complete stored procedure 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

Notice the parentheses I've added. Do not forget them!

An observation

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

You do not lose any performance benefit by using sp_executesql but rather by concatenating the parameters in the string rather than using them as a de facto parameter.

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

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