Building a nasty search sproc - 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)

Building a nasty search sproc

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2006-06-09 : 22:02:26

Hi,

I'm looking to build a sproc for my search page that deals with listboxes as well as single values. Now i've managed to complete it successfully when using single value data, but when a user expects to get multiple results from listboxes it's a no go.

For the multiple values, i've got an sql function that takes a csv (courtesey of sql team, love this site) and returns a table with those values. Then I have a proc that will use that and make sure that it brings back users who satisfy the csv.

Yes i know what you'r thinking. "WALL OF TEXT". :(
Here's my search proc so far.

CREATE PROCEDURE dbo.c_actorSearch_select
( @Page int,
@RecsPerPage int,
@GenderID tinyint = null,
@minAge tinyint = 1,
@maxAge tinyint = 99,
@PostalCode varchar(7) = null, --LIKE ?
@City varchar(25) = null,
@StateProvID tinyint = null,
@CountryID tinyint = null,
@actorType tinyint = null,
@bodyTypeIDS varchar(100) = null,
@hairColorIDS varchar(100) = null,
@eyeColorIDS varchar(100) = null,
@hairLengthIDS varchar(100) = null,
@orderBy tinyint
)
AS

Set Nocount on

Declare @Rows int

Create Table #Paging (
--Declare @Paging Table (
RowID int IDENTITY,
actorID int,
nameFirst varchar(50),
nameLast varchar(50),
nameMiddle varchar(50),
email varchar(100),
city varchar(50),
state smallint,
country smallint,
homePhone varchar(50),
mobilePhone varchar(50)
)

INSERT INTO #Paging (
actorID,nameFirst,nameMiddle,nameLast,email,city,state,country,homePhone,mobilePhone,
lastLoggedIn,gender,actorType,birthday,accountStatus)
SELECT
TOP 500
cActor.actorID, nameFirst,nameMiddle,NameLast ,email,city,state,country,homePhone,mobilePhone,
lastLoggedIn,gender,actorType,birthday,accountStatus

FROM
cActor
LEFT JOIN
cActorDetails
cad ON cad.actorID= cActor.actorID
WHERE
cActor.accountStatus = 1 and
( @GenderID IS NULL OR gender = @GenderID) and
( @minAge IS NULL OR ageYoung >= @minAge ) and
( @maxAge IS NULL OR ageOld <= @maxAge ) and
( @PostalCode IS NULL OR PostalCode = @postalCode) and
( @City IS NULL OR City like '%' + @City + '%' ) and
( @StateProvID IS NULL OR State = @StateProvID ) and
( @CountryID IS NULL OR Country = @CountryID ) and
( @actorType IS NULL OR actorType = @actorType)

ORDER BY
Case @OrderBy
WHEN 1 THEN tblActor.actorID
WHEN 3 THEN lastLoggedIn
ELSE 0 END DESC

SET @Rows = @@Rowcount

Declare @FirstRow int,
@LastRow int,
@TotalPages int

SET @FirstRow = ((@Page - 1) * @RecsPerPage) + 1
SET @LastRow = @FirstRow + (@RecsPerPage - 1)
SET @TotalPages = ceiling(Cast(@rows as decimal)/ @RecsPerPage )

SELECT
RowID, p.actorID,
birthday, actorType, nameFirst,nameMiddle,nameLast,gender,accountStatus,
@Rows TotalRows, @TotalPages TotalPages

FROM #Paging p
WHERE rowid BETWEEN @FirstRow and @LastRow

Drop Table #Paging

Set Nocount off
GO

Here is the portion that i think will be able to handle my problem, but im unsure how to work it into my exisiting search proc. (the tables and columns are just for example they aren't relevant to my search sproc)

CREATE PROCEDURE [dbo].[uc_select_dateRange]
(
@dates varchar(1000)
)
AS
SELECT
u.talentID, nameFirst,nameLast,phoneNumber,MobileNumber
FROM
dbo.parse_date_range(@dates) AS d
INNER JOIN ucAvailability u ON u.dateAvailable = d.dateAvailable
INNER JOIN ucTalent t ON u.talentID = t.talentID
GROUP BY
u.talentID,nameFirst,nameLast,phoneNumber,mobileNumber
HAVING
COUNT(u.talentID) = (SELECT COUNT(dateAvailable) FROM d(@dates))
GO

Will this work? Or is there another way. I pray to you, the sql gods. Help me please

Rob C

robc
Yak Posting Veteran

60 Posts

Posted - 2006-06-16 : 18:56:24

Hi,

I'm still stuck on this problem. I'm wondering if I have not given enough information because usually someone replies. I'm extremely desperate right now. If anyone feels they can help me out on this maybe we can make a deal. Contact me through msn @ illhouseyou@hotmail.com, or gmail as robcaruk@gmail.com

Cheers
Rob

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-06-17 : 10:39:45

Long article, many solutions: http://www.sommarskog.se/arrays-in-sql.html
Shorter article, one solution not mentioned in the first one is the most efficient when aplicable: http://www.sql-server-performance.com/mm_list_random_values.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources