locked
t-sql 2012 accept null parameter values RRS feed

  • 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

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 AM
    Answerer
  • Sunday, September 21, 2014 9:31 AM