Stored Proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Proc

Author

Topic

fzavala24
Starting Member

3 Posts

Posted - 2004-02-19 : 12:20:12

I am trying to write a search that is going to saerch based on 5 paramters. The user is not going to enter all the information needed he may enter parts of it. What is the best way of doing this.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-02-19 : 12:26:11

Make your parameters null by default.

use the following where clause

where
(@param1 is null or tbl.col1 = @param1) and
(@param2 is null or tbl.col2 = @param2) and
(@param3 is null or tbl.col3 = @param3) and
(@param4 is null or tbl.col4 = @param4) and
(@param5 is null or tbl.col5 = @param5)

you can also use the udf to csv trick to pass in value lists

and have something like tbl.col5 in (select col5 from myudf(@param5))

fzavala24
Starting Member

3 Posts

Posted - 2004-02-19 : 12:31:15

Here is what I have so far.

USE CBQMstrCopy

IF EXISTS (SELECT Name FROM sysobjects WHERE Name = 'Occupant_FetchByGuestInfo' AND Type = 'P')
DROP PROCEDURE Occupant_FetchByGuestInfo
GO

CREATE PROCEDURE Occupant_FetchByGuestInfo

@ID int,
@strFirstName varchar(50),
@strLastName varchar(50),
@strSSN varchar(50),
@strHmPhone varchar(50),
@strStatus varchar(50)

AS
SELECT
o.ID,
o.strStatus,
g.strFirstName,
g.strLastName,
g.strSSN,
g.strHmPhone,
o.dtmArrival,
o.dtmDeparture

FROM tblOccupant o INNER JOIN tblGuest g ON g.ID = o.lngGuestID

WHERE o.ID = @ID
g.strFirstName LIKE @strFirstName +'%'
OR g.strLastName LIKE @strLastName +'%'
OR g.strSSN LIKE @strSSN +'%'
OR g.strHmPhone LIKE @strHmPhone + '%'
OR o.strStatus LIKE @strStatus + '%'

ORDER BY o.strStatus, g.strFirstName, g.LastName, g.strSSN, g.strHmPhone, o.dtmArrival, o.dtmDeparture

GO

The ID is an all or nothing thing. But the other ones may or may not be entered. For example if the first and last name and a SSN are entered then only if those match a record in the DB then i need to return it. Or if just part of the name is entered anything matching must be returned.

thanks for the help.

fzavala24
Starting Member

3 Posts

Posted - 2004-02-19 : 16:01:14

Never Mind I figured out my problem thanks for all the Help guys SQL is great but i am still learning it. If you guys need any Help in VB or C ++ let me know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources