SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Writing Dynamic Stored Procedure


Writing Dynamic Stored Procedure

Author
Message
jpratt-797544
jpratt-797544
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 79
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
TheSQLGuru
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13227 Visits: 8566
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!! :-D

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Boban Stojanovski-455883
Boban Stojanovski-455883
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 95
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
Rob Reid-246754
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 446
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
Jon Monahan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3631 Visits: 162
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...
rick-507511
rick-507511
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 117
I have to say I prefer the CASE approach to code such as this. Also remember that proper indexing of tables and thoughtful arrangement of parameters according to most frequent usage probably offsets performance to some extent. Granted I would not use this technique for heavily used performance intensive situations, but this is fairly straightforward coding in cases such ad ad-hoc reporting where performance is not as critical and versatility of code is important. And that assumes that code is written to avoid ad-hoc usage returning inordinately large sets.:-)
rick-507511
rick-507511
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 117
I have to say I prefer the CASE approach to code such as this. Also remember that proper indexing of tables and thoughtful arrangement of parameters according to most frequent usage probably offsets performance to some extent. Granted I would not use this technique for heavily used performance intensive situations, but this is fairly straightforward coding in cases such ad ad-hoc reporting where performance is not as critical and versatility of code is important. And that assumes that code is written to avoid ad-hoc usage returning inordinately large sets.:-)
Andrew Peterson
Andrew Peterson
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 727
Anything we can do to encourage procs, the better. But, with the movement towards the use of object abstraction tools such as NHibernate, Linq, and MyGenerate, I fear the battle against code generated, dynamic queries will only get more difficult. Many, like EntitySpace, encourage the use of stored procedures for CRUD operations, but Hibernate does not.

The more you are prepared, the less you need it.
pablo harguindey
pablo harguindey
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
Folks:

I prefer to go in the right way of doing this kind of procedures (true dynamic querys) even if the tables involved are small. In this way if the tables keep growing nothing bad happens
sjsubscribe
sjsubscribe
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 595
http://www.sommarskog.se/dynamic_sql.html

Erland Sommarskog made a comprehensive summary of dynamic SQL issues. Very thorough and very useful for quick reference. He's also illustrated differences between versions of SQL where relevant.

He also shows when to use/avoid isNull and coalesce -- an important consideration if the search field allows nulls or not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search