Writing Dynamic Stored Procedure

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    Comments posted to this topic are about the item Writing Dynamic Stored Procedure

  • Carlo Romagnano

    SSC-Insane

    Points: 21793

    Old style to write the WHERE condition.

    I prefer to build a string ONLY with the true condition; it's more performing.

    In a complex WHERE with useless condition may lead to a BIG consume of resources.

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Quick question is it only me or has the formatting of that article gone a bit haywire. I'm sure there shouldn't be HTML formatting tags appearing alongside the SQL example.

    I used to use lots of CASE statements and IF branches to handle queries that needed to be dynamic but build on the server side using multiple proc params. For example something like this:

    CREATE PROC test

    @Stamp datetime,

    @FirstName varchar(25) = NULL,

    @Surname varchar(25) = NULL,

    @Age int = NULL,

    @Address1 varchar(40) = NULL

    AS

    BEGIN

    DECLARE @Today BIT

    SELECT @Today = CASE Datediff(day,@Stamp,getdate()) WHEN 0 THEN 1 ELSE 0 END

    IF @Today = 1

    BEGIN

    SELECT FirstName, SureName, Age, Address1

    FROM DAILY

    WHERE FirstName = CASE WHEN @FirstName IS NULL THEN FirstName ELSE @FirstName END

    AND Surname = CASE WHEN @Surname IS NULL THEN Surname ELSE @Surname END

    AND Age = CASE WHEN @Age IS NULL THEN Age ELSE @Age END

    AND Address1 = CASE WHEN @Address1 IS NULL THEN Address1 ELSE @Address1 END

    END

    ELSE

    BEGIN

    SELECT FirstName, SureName, Age, Address1

    FROM HISTORICAL

    WHERE FirstName = CASE WHEN @FirstName IS NULL THEN FirstName ELSE @FirstName END

    AND Surname = CASE WHEN @Surname IS NULL THEN Surname ELSE @Surname END

    AND Age = CASE WHEN @Age IS NULL THEN Age ELSE @Age END

    AND Address1 = CASE WHEN @Address1 IS NULL THEN Address1 ELSE @Address1 END

    END

    But then I learnt a bit more about query plan caching and indexing and so if I have to do dynamic queries in the DB I would build them up using a string with only the columns and filters required and then use sp_executeSQL to run it.

  • Gerard van de Ven

    SSC Veteran

    Points: 217

    Apart from the fact that this article is horribly mangled (which may of course not be the author's fault, but it doesn't help in making this article easily readable), it is also a rather bad advice. And I know because I had done the same a while ago but got corrected.

    For details see http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

  • Boban Stojanovski-455883

    SSC Veteran

    Points: 215

    I prefer to use Sunil's way of writing query when I have optional (can be null) parameters in the procedure. I don't see a logical reason to use CASE, or ISNULL statement.

    Sunil example is not anything new. Like anything in live, this too have some good and bad sides. The good ones are that is very logical, easy to debug, don't have a problem of sql injection etc. But also have bad side like the one that it can't use the cached plan for the query. In the other side creating query dinamically as string and executing with sp_executesql have all the bad sides but one good and that is that can use cashed plan. In most of situation we should use the first one, becuase if has more good than bad sides. But it becomes very usefull to use second one when we search very big table with milions of records.

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    http://www.sommarskog.se/dyn-search-2005.html discusses this and many other similar methods



    Clear Sky SQL
    My Blog[/url]

  • thom smith-433882

    SSC Enthusiast

    Points: 142

    Yes, it is not good way to write dynamic procedures like this because of performance, but on other side this approach has one plus - it is good readable, so easy to understand when looking on code of procedure. Times ago i was doing it same way, but once database was growing much the penalties for uising this procedures were big.

  • RichB

    SSCrazy Eights

    Points: 9651

    Excellent link Dave.

    I had been going to say I could never get the @x = y or @x is null thing to work effectively - it always ends up scanning big tables rather than using decent indexes, but Erland seems to cover the whole subject in very good depth.

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    http://www.sommarskog.se/dynamic_sql.html This one is also very good discussing dynamic sql in general. Coincidently I posted to my blog a method for optimizing dynamic sql for Unbalanced data loads http://sqlandthelike.blogspot.com



    Clear Sky SQL
    My Blog[/url]

  • Noel McKinney

    Hall of Fame

    Points: 3377

    Gail's article a few month's back on catch-all queries covered this territory really well:

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries

    Sunil's method is acceptable on small tables, but on larger tables this will choke a server. I think developers implement it in their test environment where tables have a thousand rows, and it does what it's supposed to do and returns quickly. So it goes to production where the million row tables are first encountered. That's when the stored procedure that runs in under a second during testing takes hours in actual use. Clustered index scan fun at its best. 😛

  • jpratt-797544

    SSC Eights!

    Points: 847

    This is a great, albeit rather standard, technique for conditional WHERE clauses. Performance has never been an issue for me as long as the evaluated condition is efficient (i.e. evaluate true conditions wherever possible as stated by another poster).

    My only issue with the code as presented is the use of NULL values as the parameter defaults. This is extremely risky since different tools methods of calling a stored procedure can cause different and unexpected values to be passed as parameter values.

    Instead of NULL (dates are an exception - see below), use a default value appropriate for the data type. I usually use 0 for number data types and empty string ('') for character data types. Then evaluate the parameter in the WHERE clause as ISNULL(@NumberParm, 0) or ISNULL(@StringParm, '').

    For dates, use NULL as the default and then use the ISDATE() function to make sure you get a real date. Since both '' and NULL will cause ISDATE(@DateParm) to return 0, it is safe and robust.

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Personally I hope lots of people keep on doing this because due to the staggeringly-horrible performance this type of query can have there will be more opportunities for me to get called in as a performance tuning guru to fix the mess!! 😀

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Boban Stojanovski-455883

    SSC Veteran

    Points: 215

    jpratt (6/1/2009)


    Instead of NULL (dates are an exception - see below), use a default value appropriate for the data type. I usually use 0 for number data types and empty string ('') for character data types. Then evaluate the parameter in the WHERE clause as ISNULL(@NumberParm, 0) or ISNULL(@StringParm, '').

    For dates, use NULL as the default and then use the ISDATE() function to make sure you get a real date. Since both '' and NULL will cause ISDATE(@DateParm) to return 0, it is safe and robust.

    I disagree with your comment. You have a problem when using ISNULL and default values and that is you can't have null values in columns and search by those columns too. If you do decide to not allow null values in tables then you have to use default values for columns too, which values must be in the referenced tables too. I don't see this is logical and usefull.

    Having nullable parameters is very normal to use and i can't see any problem.

  • Rob Reid-246754

    Hall of Fame

    Points: 3901

    Yes that link is a very good article that covers all the various methods and a good discussion of recompilation. As per my first example I used to write my "dynamic" queries as static SQL using CASE statements purely because they were easy to read and nice to manage. I remember a proc like the following that because of parameter difference and plan caching was taking 30 seconds+ to run. Adding the option(recompile) at the end of it fixed that problem.

    SELECT Row, CandidatePK, [Name], Email, RegisterDate,[Filename],Apps,[status]

    FROM (SELECT TOP (@Stop) ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'ASC' THEN registerDate END ASC,

    CASE WHEN @OrderBy = 'registerDate' AND @Direction = 'DESC' THEN registerDate END DESC,

    CASE WHEN @OrderBy = 'name' AND @Direction = 'ASC' THEN c.Name END ASC,

    CASE WHEN @OrderBy = 'name' AND @Direction = 'DESC' THEN c.Name END DESC)

    AS Row, c.CandidatePK, c.Name, c.Email,RegisterDate,[Filename],

    count(a.ApplicationPK) as apps, isnull(cast(cv.status as int),-1) as [status]

    FROM CANDIDATES as c

    LEFT JOIN CANDIDATES_CV as cv

    ON c.CandidatePk = cv.CandidatePK

    LEFT JOIN APPLICATIONS as a

    ON c.CandidatePk = a.CandidateFK

    WHERE c.SiteFK = @SitePK AND

    CASE

    WHEN @SearchFor is null THEN 1

    ELSE

    CASE

    WHEN @LookIn = 'name' AND c.Name LIKE @SearchFor THEN 1

    WHEN @LookIn = 'email' AND c.Email LIKE @SearchFor THEN 1

    ELSE 0

    END

    END = 1

    GROUP BY c.CandidatePK, c.Name, c.Email, RegisterDate, [Filename], [status]

    )

    AS CANDS

    WHERE Row between @Start and @Stop

    OPTION(RECOMPILE)

    I would also like to know whether the point he makes in the article which I have reproduced below is still true for SQL 2005. Providing as many options as possible for the optimiser to pick the best plan from. If there is a query that can be covered by multiple indexes should you supply as many options as possible or only provide those that you think it needs. I had a similar issue the other day in a huge mult million row logger table where the rows of data I wanted to return could be accessed by multiple clauses. Should I have added all possible paths to filter the data or not:

    Double Feature

    You may wonder why there are two conditions on the order id, customer id and product id (lines 32-33, 48-49 and 64-65). This is because I once learnt that you should always specify all conditions you know of to help the optimizer out. That was very long ago, and the rule may no longer apply. But I included it here nevertheless.

  • Jon Monahan

    SSCarpal Tunnel

    Points: 4901

    I'd agree with most of the postings - beware of dynamic SQL and make sure to check the execution plans of several different queries that get run depending upon the parameters passed in. Have to make sure that table scans are not being performed instead of indexes being utilized...

Viewing 15 posts - 1 through 15 (of 84 total)

You must be logged in to reply to this topic. Login to reply