SQL Server Performance

Dynamic SQL in Stored Procedure

Discussion in 'SQL Server 2005 General Developer Questions' started by danny123, Jul 28, 2008.

  1. danny123 New Member

    Hi there,
    I have one stored procedure which have 5 input variables and all of those can be null. Now i need to write the query in the stored procedure so that which ever input variable is having value supplied that parameter will be used in query to filter the data.
    One way of dealing with this is i create a dynamic sql and execute that in the end. Is there any other way i can deal with this?
    Thanks !
  2. walkair New Member

  3. Adriaan New Member

    [quote user="walkair"]Try this one - http://www.sqlteam.com/article/implementing-a-dynamic-where-clause. In this great article, Garth Wells is showing how to implement dynamic where clause without using dynamic T-SQL.[/quote]I notice this Garth character is using COALESCE(@variable,column) for handling NULL parameters in the WHERE clause. Note that this type of expression will result in table scans, so it will often harm performance. The dynamic SQL option usually works out better for this.
  4. walkair New Member

    Adriaan,
    IMO, cached table scans are better than non-cached table seeks.
    Best Regards.
  5. satya Moderator

    I would like to ask the OP that how many rows are involved here?
    The main difference between ISNULL vs COALESCE is, ISNULL() returns the same data type of its first parameter, but COALESCE() returns the data type of the parameter with the highest data type precedence. So within the code how many times you can use the ISNULL, based on the above difference I would go with COALESCE only.
    Referring the same on BOL:
    ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. In SQL Server, to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute
  6. preethi Member

    Just a quick reply:- If anyone thinks of COALESCE, just test it again. I have used it in the past but found that it tookmore time than ISNULL. We later replaced it with ISNULL.
  7. FrankKalis Moderator

    Here is another (and in my opinion more complete) discussion on this topic: http://www.sommarskog.se/dyn-search.html
  8. preethi Member

    I have a couple of questions:1. You said you have 5 parameters: Are they related to the same table?2. Do those columns (to which the parameters refer) indexed?There is a hard but best performing way: Use IF clause. In your case there are 32 possible scenarios (with 5 parameters from all NULL to all supplied) to which you may not like to write code :) You may have to create code based on your answers to the above questions.Finally, why should you take all this burden into the database? Cant the application check what values are null and call seperate stored procedures based in the parameters. It will remove atleast some burden from the database.
  9. Adriaan New Member

    The performance issue (if it occurs) will be caused by the fact that you are adding a function that evaluates a (set of) column(s) inside the WHERE statement. It doesn't really matter if you use COALESCE or ISNULL, the point is not to use functions if you can simply compare expressions.

Share This Page