Sql stored procedure parameter null

I am creating a stored procedure to do a search through a table. I have many different search fields, all of which are optional.

Is there a way to create a stored procedure that will handle this? Let's say I have a table with four fields: ID, FirstName, LastName and Title.

I could do something like this:. This sort of works. However it ignores records where FirstName, LastName or Title are NULL.

How to create a SQL Server stored procedure with parameters

If Title is not specified in the search parameters I want to include records where Title is NULL - same for FirstName and LastName. I know I could probably do this with dynamic SQL but I would like to avoid that.

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan use an index. Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:.

Stored procedures with optional parameters that can be null

Dynamic Search Conditions in T-SQL by by Erland Sommarskog. The Curse and Blessings of Dynamic SQL by Erland Sommarskog. If you have the proper SQL Server version SQL SP1 CU5 This will work for any SQL Server version return proper results , but only include the OPTION RECOMPILE if you are on SQL SP1 CU5 The OPTION RECOMPILE will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance.

If not on that version of SQL Server , just leave that line off. The answer from KM is good as far as it goes but fails to fully follow up on one of his early bits of advice;. If you are looking to achieve the best performance then you should write a bespoke query for each possible combination of optional criteria.

This might sound extreme, and if you have a lot of optional criteria then it might be, but performance is often a trade-off between effort and results.

In practice, there might be a common set of parameter combinations that can be targeted with bespoke queries, then a generic query as per the other answers for all other combinations.

The advantage of this approach is that in the common cases handled by bespoke queries the query is as efficient as it can be - there's no impact by the unsupplied criteria. Also, indexes and other performance enhancements can be targeted at specific bespoke queries rather than trying to satisfy all possible situations.

It is mentioned in the provided links of the accepted answer, but I think it deserves an explicit answer on SO - dynamically building the query based on provided parameters. Usually, these filtering stored procedures do not float around, but are being called from some service layer. This leaves the option of moving away business logic filtering from SQL to service layer.

By posting your answer, you agree to the privacy policy and terms of service. Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list.

Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us.

Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: How can I use optional parameters in a T-SQL stored procedure? I could do something like this: Corey Burnett 3, 7 40 Have a look here: Try following where statement: If you want to get all title if input parameter is null, then try something like that: Your best method will depend on your parameters, your data, your schema, and your actual usage: Dynamic Search Conditions in T-SQL by by Erland Sommarskog The Curse and Blessings of Dynamic SQL by Erland Sommarskog If you have the proper SQL Server version SQL SP1 CU5 AND has precedence over OR, so without the proper brackets this example won't produce the expected results So it shoudl read: Bliek, thanks I fixed it.

tsql - How can I use optional parameters in a T-SQL stored procedure? - Stack Overflow

Oct 10 '11 at Your answer is great. Thanks for all the info! Your answer is extremely clean and simple.

The answer from KM is good as far as it goes but fails to fully follow up on one of his early bits of advice; Rhys Jones 3, 1 8 Extend your WHERE condition: Five years late to the party.

One example is using LINQ2SQL to generate the query based on provided filters: CreatedById ; if filters. No parameter sniffing or recompile hints needed somewhat easier to write for those in the OOP world typically performance friendly, since "simple" queries will be issued appropriate indexes are still needed though Cons: LINQ2QL limitations may be reached and forcing a downgrade to LINQ2Objects or going back to pure SQL solution depending on the case careless writing of LINQ might generate awful queries or many queries, if navigation properties loaded.

Alexei 6, 10 24 Sign up or log in StackExchange. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. In it, you'll get: The week's top questions and answers Important community announcements Questions that need answers. Stack Overflow works best with JavaScript enabled. LINQ2QL limitations may be reached and forcing a downgrade to LINQ2Objects or going back to pure SQL solution depending on the case careless writing of LINQ might generate awful queries or many queries, if navigation properties loaded share improve this answer.

MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3. Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.