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]"

Answer 1

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).

Answer 2

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

Answer 3

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 !

Answer 4

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

Answer 5

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