CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012dynamic-sql
Josh B
Josh B

Reputation: 324

Alternative to dynamic SQL

I have a couple of stored procedures which use dynamic SQL. I'd really like to alter them so that they aren't dynamic, purely because they can be very annoying to troubleshoot and alter due to the error handling (clicking the error message doesn't take you to the error). I know I can select the text and paste it as regular SQL to help with this, but it's quite frustrating.

The problem I'm having with this is that the queries run much slower when they are not dynamic. Specifically, the where clause is much faster in the dynamic query due to its increased flexibility. For example, the static where clause would be something like:

where
     SomeColumn = case when @variable1 = 0 then SomeColumn else @variable1 end
and(
    (@variable2 = -2 and SomeColumn2 = 1)
    or (@variable2 = -1)
    or (@variable2 = 0 and SomeColumn2 = 0 and SomeColumn3 = 0)
    or (@variable2 = 1 and SomeColumn2 = 0 and SomeColumn3 > 0)
    )

But the dynamic where clause would be:

where ' + @SomeCondition + @SomeCondition2 + '

Using a case statement like this:

declare @SomeCondition nvarchar(max) = case 
    when @variable3 = -2 then N'Condition 1'
    when @variable3 = 0 then N'Condition 2'
    when @variable3 = 1 then N'Condition 3'
    else N''
    end

The only solution I can think of is using multiple if statements and only changing the where clause in each statement, but this seems incredibly wasteful and time consuming.

Are there any other alternatives to dynamic SQL? Failing that, is there something I can do to get sql-server to direct me to the error properly?

Upvotes: 3

Views: 2943

Answers (2)

Mit Bhatt
Mit Bhatt

Reputation: 482

Alternatively, you can use ISNULL(NULLIF syntax as below. But use it with caution as it might impact the performance negatively.

where
     SomeColumn = ISNULL(NULLIF(@variable1,''),SomeColumn) and
     SomeColumn2 = ISNULL(NULLIF(@variable2,''),SomeColumn2) and 
     and so on..

Upvotes: 1

dean
dean

Reputation: 10098

Add the OPTION (RECOMPILE) to the query. This will cause it to be recompiled on every execution, and the optimizer is smart enough to shortcut and eliminate the predicates, very much like you're doing yourself now with dynamic SQL.

Upvotes: 3

Related Questions