Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Browse by Tags

Related Posts
  • Blog Post: Optional Parameters: Adam Hopes I'm Joking, But The Joke's On Me

    This is absolutely my last gasp on optional parameters. Adam Machanic left some syntax in a comment here which shows just how far off base my "last gasp" in this post was. My syntax produced 594 logical reads to resolve a query while the equivalent dynamic SQL handled the same issue in six logical...
  • Blog Post: Database Programming: Yet More On Optional Parameters

    Ever since I mentioned that Calvin Hsia clued me in to the size of this blog's audience, the comment traffic here has seen a notable spike. I love it.. In case you've missed it in the comment stream from the original post in this thread, Adam Machanic left a comment commending an article on this topic...
  • Blog Post: Database Programming: Optional Parameters Revisited

    Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored procedure (the earlier posts are here , here , and here ). The question had basically boiled down to whether there was single-statement syntax available which would preclude the necessity to use dynamic...
  • Blog Post: Database Programming: Nearing Capitulation On Optional Parameters

    Adam and Tony both left comments indicating that either flavor of my proposed optional parameter syntax will result in a table scan, and that performance will (to appropriate Tony's characterization) suck. That's quite correct. There's one more syntax construction I need to take a look at, and I'll...
  • Blog Post: Database Programming: Implementing Optional Parameters To A Stored Procedure

    A colleague asked the following question recently: I have a stored procedure that takes four parameters that are optional. My WHERE clause is supposed to include those parameters that are NOT NULL. If, for example, only @p1 and @p3 have a value, the query will be: SELECT x FROM dbo . foo WHERE...
  • Blog Post: Database Programming: An Incrementally Better Mousetrap For Optional Parameters

    enderC points out that the syntax I proposed in last night's post on optional parameters can be made more readable thusly.. SELECT x FROM dbo . foo WHERE COALESCE ( y1 , @constant ) = COALESCE ( @p1 , y1 , @constant ) AND COALESCE ( y2 , @constant ) = COALESCE ( @p2 , y2 , @constant ) ...
  • Blog Post: Database Programming: One Last Gasp On Optional Parameters

    July 12 update: Everything that follows is WRONG . See Adam's comment and my follow-up . You might think I'm obsessing over this, and you may well be right, but I wanted to make one more comment about handling optional parameters. The issue with the syntax I initially proposed was that it didn...
  • Blog Post: Database Programming: No Better Options For Optional Parameters

    Well, the current leg of the great "optional parameter code-off" has been completed. You definitely want to party with me on a national holday. :) Previous posts in this thread (many filled with questionable advice, as least that coming from me) may be found here , here , here , here , here , and here...
  • Blog Post: Database Programming: Just What Were Those Performance Implications, Anyway?

    Last Thursday, when I offered a solution for implementing optional parameters to a stored procedure , I blithely stated, "this approach is not without performance implications," and then let the issue rest. Well, Brian noticed and posted a comment asking for the details. I've responded to him in the...