Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Multiple parameters Expand / Collapse
Author
Message
Posted Monday, March 08, 2010 7:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 20, 2010 11:25 PM
Points: 41, Visits: 84
Student Table Structure

Firstname Lastname FullName Age
ABC XYZ ABCXYZ 12
DEF GHI DEFGHI 14
JKL MNO JKLMNO 16

I create an SP as below
Create procedure
SearchStudent
(@i_firstname varchar(50),
@i_lastname varchar(50),
@i_fullname varchar(50),
@i_age int)

The search can be done by either using Firstname or Lastname or Fullname or Age.

For e.g if I'm searching by providing the Firstname alone then the other parameters are blank/null...

select firstname,lastname,fullname,age
from student
where firstname like '@i_firstname%'
and lastname like '@i_lastname%'
and fullname like '@i_fullname%'
and age = @i_age

How the search can be achieved by providing any one of the parameter?



Kindest Regards,

Arunkumar


Reputation is what other people know about you. Honor is what you know about yourself."--


Post #879040
Posted Monday, March 08, 2010 7:51 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Please read this Article: Example of Safely Using Dynamic SQL with User Selected Table Name. Just do what it says, except for your column name instead of the table name.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #879048
Posted Monday, March 08, 2010 10:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 11,168, Visits: 10,930
...and if you would like some more options, take a look at Erland's article on the subject: http://www.sommarskog.se/dyn-search-2005.html



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #879110
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse