help with 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)

help with stored proc

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2003-08-01 : 20:42:42

I am attempting to create a search that allows the searching of multiple interests. This procedure I have right now works well for one interest but I would like to increase the functionality so I can pass a comma delimited string to it, and it parses the string and then searchs for records that contain all of the passed data.

My Interests part has 2 tables
sample

tblinterests
userid / interestID

500 / 1
500 / 2

tblInterestDetails
interestID / interest

1 / sqlteam
2 / beer

I'm pretty lost on where to even start, can anybody lend a hand or offer some suggestions.

Thanks alot!,

Mike123

CREATE PROCEDURE select_Interests_search
(
@interest varchar(50)
)
AS SET NOCOUNT ON
SELECT u.userid, u.nameonline FROM tblUserDetails u INNER JOIN tblInterests i on u.userID = i.userID JOIN tblInterestDetails on i.interestID = tblInterestDetails.interestID WHERE tblInterestDetails.interest= @interest

GO

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-01 : 21:38:00

Suggest you purloin the peerless paper putforward purposely to parse CSV:

[url]http://sqlteam.com/item.asp?ItemID=2652[/url]

CREATE PROCEDURE select_Interests_search
(
@interest varchar(50)
)
AS SET NOCOUNT ON

CREATE TABLE #searchtable (interest VARCHAR (100))
-- Populate #searchtable with @interest here then

SELECT u.userid, u.nameonline

FROM tblUserDetails u

INNER JOIN tblInterests i on u.userID = i.userID

INNER JOIN tblInterestDetails ID on i.interestID = ID.interestID

INNER JOIN #searchtable S ON S.interest = ID.interest

GROUP BY u.userid, u.nameonline

There are other ways to get the same result:


CREATE PROCEDURE select_Interests_search
(
@interest varchar(50)
)
AS SET NOCOUNT ON

CREATE TABLE #searchtable (interest VARCHAR (100))
-- Populate #searchtable with @interest here then 


SELECT u.userid, u.nameonline

FROM   tblUserDetails u 
WHERE  u.userid IN (
     SELECT userid FROM tblinterests where interestid IN (
        SELECT interestID FROM tblInterestDetails WHERE interest IN (
            SELECT interest FROM #searchtable
          )
     )
)

Sam

xtsmats
Starting Member

6 Posts

Posted - 2003-08-06 : 13:00:26

Hi,

I find this easier to do and without using a temp table.

CREATE PROCEDURE select_Interests_search
(
@interest varchar(100)
)
AS
begin
SET NOCOUNT ON

declare @bepa nvarchar(200)

select @interest = "'" + replace(@interest,",","','") + "'"
select @bepa = "select * from kundroll where ftgnamn in (" + @interest + ")"

exec sp_executesql @bepa

end

Regards,
Mats Larsson
Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-06 : 13:06:49

quote:
Originally posted by xtsmats

Hi,

I find this easier to do and without using a temp table.

CREATE PROCEDURE select_Interests_search
(
@interest varchar(100)
)
AS
begin
SET NOCOUNT ON

declare @bepa nvarchar(200)

select @interest = "'" + replace(@interest,",","','") + "'"
select @bepa = "select * from kundroll where ftgnamn in (" + @interest + ")"

exec sp_executesql @bepa

end

Regards,
Mats Larsson
Sweden

Just because something is easier to do doesn't mean that you should do it. You are using dynamic sql in your solution. How is that better?

Tara

xtsmats
Starting Member

6 Posts

Posted - 2003-08-06 : 15:29:29

I have seen problem with using to many temp tables. In this case that may not be a problem but I have solved some problem at a customer site that where using to many temp tables (and cursors) and had problems with lots off deadlocks.
So I try not to use temp tables when there is a nother solution. And you are right I haven't tried to use to much of dynamic SQL. 8)

Mats

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-06 : 19:00:39

Would you qualify ONE temp table as too many? That's all that Sam's solution uses.

shsmonteiro
Constraint Violating Yak Guru

290 Posts

Posted - 2003-08-06 : 20:47:04

and if you don't like temp tables, what about table types?

Sérgio Monteiro
Trust in no Oracle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources