Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Writing Dynamic Stored Procedure Expand / Collapse
Author
Message
Posted Monday, June 1, 2009 6:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:21 AM
Points: 21, Visits: 46
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.
Post #726569
Posted Monday, June 1, 2009 6:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 4,436, Visits: 6,337
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 at GMail
Post #726582
Posted Monday, June 1, 2009 6:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:16 AM
Points: 59, 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.
Post #726594
Posted Monday, June 1, 2009 6:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
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.
Post #726604
Posted Monday, June 1, 2009 6:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:30 AM
Points: 2,737, Visits: 113
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...
Post #726606
Posted Monday, June 1, 2009 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 17, 2011 7:58 AM
Points: 29, 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.
Post #726618
Posted Monday, June 1, 2009 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 17, 2011 7:58 AM
Points: 29, 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.
Post #726619
Posted Monday, June 1, 2009 7:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:57 PM
Points: 202, Visits: 392
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.
Post #726623
Posted Monday, June 1, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 28, 2010 12:12 PM
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
Post #726645
Posted Monday, June 1, 2009 7:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589
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.
Post #726656
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse