How to make parameter value optional in stored procedure

I would like this sp to return all rows where the surname = @name and allow the user to enter specific first name(@Gname or not. if user enters a first name then return all rows that match firstname surname else return all rows where surname matches name.


create proc spHPI

@Gname varchar(30),
@Name varchar(30)
as
      select
      n.[given name],
      n.surname,
      n.[professional ID],
      p.[Common Person Number],
      a.street_address,
      a.city,
      q.qualification
      from name n
      left    outer join address a  on n.[professional id]= a.[professional id]      
            join Professional p   on n.[Professional ID] = p.[Professional ID]
            join qualification q   on q.[Professional ID]= p.[Professional ID]
      where n.surname = @Name
      and n.[given name] = @Gname
      order by n.[given name]
deNZityAsked:
Who is Participating?
 
DimitrisSenior Solution ArchitectCommented:
here u r
create proc spHPI
@Name varchar(30),
@Gname varchar(30) = null,
as
      select
      n.[given name],
      n.surname,
      n.[professional ID],
      p.[Common Person Number],
      a.street_address,
      a.city,
      q.qualification
      from name n
      left    outer join address a  on n.[professional id]= a.[professional id]      
            join Professional p   on n.[Professional ID] = p.[Professional ID]
            join qualification q   on q.[Professional ID]= p.[Professional ID]
      where n.surname = @Name
      and n.[given name] = isnull(@Gname, n.[given name])
      order by n.[given name]
 
 
 
/* You can call it like 
exec spHPI 'dimitris' or exec spHPI @Name ='dimitris' 
or
exec spHPI 'dimitris','FromGreece' or exec spHPI @Name ='dimitris', @gname ='FromGreece'
 
The second parameter (@gname has a default value of null 
so ig you don't pass  it (first exec method) it uses the null 
 
*/

Open in new window

0
 
deNZityAuthor Commented:
Thanks I changed it a bit but just what I wanted.

create proc spHPI
@Name varchar(30),
@Gname varchar(30) = null
as
      select
      n.[given name],
      n.surname,
      n.[professional ID],
      p.[Common Person Number],
      a.street_address,
      a.city,
      q.qualification
      from name n
      left    outer join address a  on n.[professional id]= a.[professional id]      
            join Professional p   on n.[Professional ID] = p.[Professional ID]
            join qualification q   on q.[Professional ID]= p.[Professional ID]
      where n.surname like @Name + '%'
      and n.[given name] like isnull(@Gname + '%', n.[given name])
      order by n.[given name]
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.