Search
InstantDoc ID
GO

Browse By: Author | Issue

November 12, 2009 04:12 PM

Passing NULL Parameters

How to handle the unknown
Rating: (0)
William Vaughn
SQL Server Magazine
InstantDoc ID #102592
Executive Summary:
Have you ever had a stored procedure that could accept a NULL value as a parameter but didn't seem to work correctly when you did so? You can handle NULL parameters several ways, one of which is to force the use of default parameter values. This technique presents developers with a handy option when designing applications that use ADO.NET to execute T-SQL stored procedures with optional named parameters.

...

...This article is for paid Professional Members only.

Already a Professional Member? Please log in now:

NOT A PROFESSIONAL MEMBER? YOU CHOOSE:

Professional Membership

Monthly

Annual

VIP Membership

Monthly

Annual

Compare Member Benefits

Add a Comment

A good way is to use dynamic SQL or split into multiple stored procedure. This is to make sure the optimum query plan is generated

benoityip 12/8/2009 6:08:23 AM

Try to advoid using this technique. There is a big limitation in performance where most people do not realise. This technique will stuff up the query plan. It will do an index scan instead of index seek. I have fixed this kind of query several times because of this technique. This only occur in the following condition I described: If the WHERE clause in the SQL do not contain any non-nullable values (the parameter must have something).. Page 1 of this article is a good example... Index scan will occur..

benoityip 12/8/2009 6:00:35 AM

You must log on before posting a comment.

Are you a new visitor? Register Here

© 2010 Penton Media, Inc.

Windows is a trademark of the Microsoft group of companies. Windows IT Pro is used by Penton Media Inc. under license from owner.