Multiple parameters

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Multiple parameters

Multiple parameters

Rate Topic

Display Mode

Topic Options

Author

Message

CrazyMe

Posted Monday, March 08, 2010 7:29 PM

SSC 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

RBarryYoung

Posted Monday, March 08, 2010 7:51 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

Paul White

Posted Monday, March 08, 2010 10:17 PM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:18 PM
Points: 10,989, Visits: 10,532

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

Permissions