Answered by:
t-sql 2012 accept null parameter values

Question
-
The following t-sql 2012 works fine in sql management studio. However when I place it in a .net 2010 web form application, I am told the sql does not work when the parameter values are null. Thus can you tell me what I can change in the sql below that will accept null as 3 possible input values?
SELECT i.[lastName]
,i.[firstName]
,i.[middleName]
,i.[suffix]
,a.[userid]
,a.schoolnum
,a.spa
,a.mrref
from [OPS].[dbo].[Identity] i
inner join [OPS].[dbo].[Person] p on i.identityID = p.currentIdentityID
inner join [OPS].[dbo].[UserAccount] u on u.personID = p.personID
inner join [CampusOps].[dbo].[AtnLtrUsers] a on a.userid =u.username
where (i.[lastName] like '%' + @lname + '%' or i.[firstName] like'%' +
@fname + '%'
or i.[middleName] like'%' + @mname + '%')
or (@fname is null and @lname is null and @mname is null)
order by i.[lastName], i.[firstName], i.[middleName]"Saturday, September 20, 2014 6:04 PM
Answers
-
Can you use a stored procedure instead of a query?
Blog: Inserting Form Data Into DataBase Using Stored Procedure In ASP.NET C#
Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014
- Marked as answer by wendy elizabeth Monday, September 22, 2014 12:43 AM
Sunday, September 21, 2014 9:31 AM
All replies
-
You should create a stored procedure for this.
create procedure GetNames (@lname varchar(max) = null, @fname varchar(max) = null, @mname varchar(max) = null) as begin SELECT i.[lastName] ,i.[firstName] ,i.[middleName] ,i.[suffix] ,a.[userid] ,a.schoolnum ,a.spa ,a.mrref from [OPS].[dbo].[Identity] i inner join [OPS].[dbo].[Person] p on i.identityID = p.currentIdentityID inner join [OPS].[dbo].[UserAccount] u on u.personID = p.personID inner join [CampusOps].[dbo].[AtnLtrUsers] a on a.userid =u.username where (i.[lastName] like '%' + @lname + '%' or @lname is null) and (i.[firstName] like'%' + @fname + '%' or @fname is null) and (i.[middleName] like'%' + @mname + '%' or @mname is null) order by i.[lastName], i.[firstName], i.[middleName] end
Also keep in mind that NULL is different than an empty string but it should work exactly like a null anyway (unless your columns/fields are also NULLable).Saturday, September 20, 2014 6:14 PM -
Please be aware that writing a logic as in above suggestion may cause QA to generate bad plans
see
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
Saturday, September 20, 2014 6:17 PM -
Like with (leading) % is not sargable anyway. Which means prepare yourself for scanning.
You could accelerate your query a little and have better plans if you did separate queries with cases.
Have faith in query optimizer but keep an open mind !
- Edited by cnk_gr Saturday, September 20, 2014 6:25 PM
Saturday, September 20, 2014 6:23 PM -
If you try to build a SELECT statement based on the input parameters, take a loom at Erland's article for dynamic search here
http://www.sommarskog.se/
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Sunday, September 21, 2014 9:20 AMAnswerer -
Can you use a stored procedure instead of a query?
Blog: Inserting Form Data Into DataBase Using Stored Procedure In ASP.NET C#
Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014
- Marked as answer by wendy elizabeth Monday, September 22, 2014 12:43 AM
Sunday, September 21, 2014 9:31 AM