0

It looks like the need to use dynamic SQL is unavoidable with SQL Server for different reasons but the resulting code looks convoluted and unmaintainable. SQL server management studio's intellisence stops working for queries inside literals and overall with all this string manipulation stuff it's a mess IMO.

What are your approaches to simplifying this and making the SQL more maintainable?

(except of course not using SQL directly at all and relying on an ORM which is not an option in my current situation)

Edit: In my current particular case I have an SP with several filtering parameters that are used in the where clause with like, when a parameter is null or empty it should not be included in the where clause, trying to avoid dynamic sql with (@param IS NULL) OR (column LIKE @param) leads to poor performance.

axk
  • 5,316
  • 12
  • 58
  • 96
  • Dynamic SQL within your app? Or dynamic tsql (in stored procs)? Or both? – Dave Mason Feb 05 '15 at 16:26
  • (Re: _"It looks like the need to use dynamic SQL is unavoidable with SQL Server"_ – it might *look* that way, but are you actually *certain*? If indeed dynamic SQL is the only sensible solution to whatever you're doing, then no, there is *no way* to get proper IntelliSense for such a statement that is split & distributed over several string literals, which are possibly out of sequence. You'll have to live with that.) – stakx - no longer contributing Feb 05 '15 at 16:46
  • 1
    It sounds like you're dealing with a "catch-all" query. IMO dynamic tsql **is** the way to go--it should lead to better performance. [Here's a good article](http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/) that explains the pros and cons of multiple predicates for optional params vs dynamic tsql. – Dave Mason Feb 05 '15 at 17:53

1 Answers1

1

Not really much of an answer but this is how I do it
Gut it out
I user StringBuilder for efficiency and to get it to line up.
Copy paste to SSMS to clean up syntax

sbSQLtotal.AppendLine("select #tempCoreAll.[sID], #tempCoreAll.[sParID] ");
sbSQLtotal.AppendLine("  from #tempCoreAll ");
sbSQLtotal.AppendLine(" order by #tempCoreAll.[iden]; "); 
Debug.WriteLine(sbSQLtotal.ToString());
paparazzo
  • 44,497
  • 23
  • 105
  • 176