Alternative to dynamic SQL

Question

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?

Are you using `try`/`catch` to get the errors caused by the dynamic SQL? Perhaps fixing the error handling will do what you need. Even multiple `if`s could be problematic unless you use the `recompile` option. — Gordon Linoff, Feb 10 '17 at 11:38
That would be extremely helpful, the frustrating error handling is pretty much the only reason I want to change the dynamic queries. Would I just use try/catch within the variable containing the SQL, or does it need to be written outside it? I'm not entirely sure how to go about it with dynamic SQL. — Josh B, Feb 10 '17 at 11:42
You could make specialized, optimized, procedures and then call the proper one based on your parameters, this way you'll avoid the OR conditions. But then you'll have more procedures to maintain. In my experience, you'll never get a "one-size-fits-all" query (or-or-or-or pattern in the where) to function as well as dynamic or specialized queries. — Allan S. Hansen, Feb 10 '17 at 11:54

score 3 · Accepted Answer · answered Feb 10 '17 at 11:51

3

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.

answered Feb 10 '17 at 11:51

dean

9,960
2
25
26

This made my query much faster (thanks!), but on clicking the error message it still brings me to a random spot in the query. I'm adding `option (recompile)` at the end of the query within the SQL variable that is executed. – Josh B Feb 10 '17 at 12:03
With complex queries, line number of where error occurred could be deceiving. Also, you could be bumping into this known issue: https://connect.microsoft.com/SQLServer/feedback/details/857794/. Anyway, glad this helped with performance at least. – dean Feb 10 '17 at 12:16
My query is structured like this: `declare @SQLText nvarchar(max) = N'BigQueryGoesHere option (recompile)' exec sys.sp_executesql @SQLText` Should this return the error correctly? I'm directed to the right place when I select the query text and paste it as standard SQL. – Josh B Feb 10 '17 at 14:15
I don't understand, why don't you just run it as a static SQL, instead of this dynamic approach with sp_executesql? – dean Feb 10 '17 at 14:19
The dynamic query is much quicker and more flexible, mainly because of the flexibility of the where clause. I'll edit my original post to make it clearer. – Josh B Feb 10 '17 at 14:32
Using `option (recompile)` with static sql is still slightly slower than using dynamic SQL, but I can always change to static if the performance increase isn't worth my frustration. – Josh B Feb 10 '17 at 15:08

score 1 · Answer 2 · answered Feb 10 '17 at 11:57

1

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..

answered Feb 10 '17 at 11:57

Mit Bhatt

472
2
11

I have tried this, but unfortunately it did slow down the query significantly. – Josh B Feb 10 '17 at 12:01

Alternative to dynamic SQL

2 Answers2