So I'll have to define my @SpecialtyID as nvarchar in the SP and pass it as such, correct?
Is this considered a dynamic query?
Thanks.
Main Topics
Browse All TopicsOk I am not sure how to handle this query. Your help would be greatly appreciated.
I am trying to return a bunch of search results which are more or less strait forward, except that part of the results I am trying to get at could be multiple records in a related table. i'll try and explain:
I have a main table which stores info about a Photographer - this table relates to another which has a list of their specialties - so it looks like this :
Specialties Table
PhotographerID SpecialtyID
1 1
1 2
1 3
2 1
2 2
Now, here's the catch a person doing a search is allowed to search for more than one specialty - so I am trying to pass this to the SP as a comma deliminated string (1,2,3) So the search is looking for a Photographer who specializes in these 3 specialties. If it were just one then I could do a sub query - but how do you handle it in this case? I have used CharIndex in the past, but that was comparing a deliminated string against one value, where here there may be several.
Here's my attempt so far. But this only works if I pass one specialtyID and then I have to use Distinct as well
Alter PROCEDURE "sp_PagedItems_Listings"
(
@Creator nvarchar(80) = null,
@DiciplineID int,
@SpecialtyID int, - this should probably be an nvarchar with the direction I am heading
@FacilityID int,
@Location nvarchar(500),
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
FirstName nvarchar(50),
LastName nvarchar(50)
)
INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName)
Select Distinct Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName
From Listings
Inner Join Users On Users.UserID = Listings.UserID
Inner Join ListingSpecialtyDetails On ListingSpecialtyDetails.Li
Where (ListingSpecialtyDetails.S
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Ok I tried it but get the following error..
"Syntax error converting the varchar value '+ @SpecialtyID + ' to a column of datatype int.
Here is my SQL:
Alter PROCEDURE sp_PagedItems_Listings
(
@Creator nvarchar(80) = null,
@DiciplineID int,
@SpecialtyID nvarchar(500),
@FacilityID int,
@Location nvarchar(500),
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
FirstName nvarchar(50),
LastName nvarchar(50),
Thumbnail nvarchar(100),
Specialties nvarchar(500)
)
INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName, Specialties)
Select Distinct Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName, Listings.Specialties
From Listings
Inner Join Users On Users.UserID = Listings.UserID
Inner Join ListingSpecialtyDetails On ListingSpecialtyDetails.Li
Inner Join ListingFacilitiesDetails On ListingFacilitiesDetails.L
Where (ListingSpecialtyDetails.S
Also, please maintain your many open questions:
1 04/05/2003 50 SubDirectories and ASP Unlocked Active Server Pages (ASP)
2 04/08/2003 400 Trailing spaces in table cells on input ... Unlocked Microsoft SQL Server
3 05/21/2003 125 Link to an anchor in a parent frameset u... Unlocked JavaScript
4 06/09/2003 50 File System Object Unlocked Active Server Pages (ASP)
5 06/25/2003 50 Layers visibility question Unlocked JavaScript
6 07/15/2003 50 Document.Write question Unlocked JavaScript
7 09/04/2003 50 Proper example of trigger which deletes/... Unlocked Microsoft SQL Server
8 05/03/2003 250 Javascript trouble with 'is null or not ... Unlocked JavaScript
9 06/16/2003 50 Replace function Unlocked JavaScript
10 07/06/2003 125 Query does not work with input parameter... Unlocked Microsoft SQL Server
11 07/17/2003 50 Safari doesn't like my java Unlocked JavaScript
12 08/22/2003 125 Sp Cursor For not looping Unlocked Microsoft SQL Server
Thanks,
Anthony
How many different specialities do you REALLY need to allow them to search for at one time? I know that's risky, but if you could set a limit, say four, then you could create four variables to hold the values and avoid a temp table.
If this is SQL 2K, you can use a table variable which is much less overhead than a temp table. Is so, do that, and allow them to enter as many as they want. Example using table variable to follow ASAP.
no more like this....
get all the people who have the specialty....
then count the specialty searched for
then display only those who have all of those ( count(*) = number of specialty)
... I assume that you'll cater for photographers moving in and out of specialities at some stage...
Alter PROCEDURE "sp_PagedItems_Listings"
(
@Creator nvarchar(80) = null,
@DiciplineID int,
@SpecialtyID int, - this should probably be an nvarchar with the direction I am heading
@FacilityID int,
@Location nvarchar(500),
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #Temp
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
specialtyid int
,kount integer
)
SElect @speciality = Case when @specialtyid is null then null else ',' + @specialityid + ',' end
INSERT INTO #Temp (ListingID, ListingTypeID, UserID,kount )
Select L.ListingID, L.ListingTypeID, L.UserID , lsd.specialtyid,1
From Listings as L
Inner Join ListingSpecialtyDetails as LSD On LSDetails.ListingID = L.ListingID
Where @SPECIALTYID LIKE '%,' + CONVERT(varchar(10),Lsd.Sp
declare @curPos int,@frompos int,@K int
set @frompos=1,@K=1
While charindex(',',@specialtyid
begin
@curpos=charindex(',',@spe
@K = @k +1
set @frompos=@curpos+1
End
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
FirstName nvarchar(50),
LastName nvarchar(50)
)
insert into #Tempitems
Select t.ListingID,t.Listing.Type
, u.firstname,u.lastname
from users as U
Inner Join (select t.ListingID,t.Listing.Type
from #temp as t
Group by t.ListingID,t.Listing.Type
Having count(*) = @k) as T
on t.userid=U.userid
return
Maybe something like this:
--...
DECLARE @SpecialtyIDs VARCHAR(200)
--...
SET NOCOUNT ON
SET @SpecialtyIDs = '1, 2, 3'
DECLARE @SpecialtyId INT
DECLARE @DelimFound INT
DECLARE @specIds TABLE (specId INT)
WHILE LEN(@SpecialtyIDs) > 0
BEGIN
SET @DelimFound = CHARINDEX(',', @SpecialtyIDs)
IF @DelimFound > 0
BEGIN
SET @SpecialtyId = LEFT(@SpecialtyIDs, @DelimFound - 1)
SET @SpecialtyIDs = SUBSTRING(@SpecialtyIDs, @DelimFound + 1, LEN(@SpecialtyIDs))
END --IF
ELSE
BEGIN
SET @SpecialtyId = @SpecialtyIDs
SET @SpecialtyIDs = ''
END --ELSE
INSERT INTO @specIds (specId) VALUES(@specialtyId)
END --WHILE
--SELECT * FROM @specIds
INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName)
Select Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName
From Listings
Inner Join Users On Users.UserID = Listings.UserID
WHERE (SELECT COUNT(*)
FROM ListingSpecialtyDetails LSD
WHERE Listings.ListingID = LSD.ListingID
INNER JOIN @specIds AS specList ON LSD.SpecialtyID = specList.specId)
= (SELECT COUNT(*) FROM @specIds)
You may have to tweak the final query, but the general idea is that the number of specialities THAT MATCH for the current ListingID must match the total number of specialty ids specified, that is, someone must match all to be listed.
Ok Here's a new tack but I think it leads me in the same direction. The Query makes more sense (to me) but I still run into the problem of passing 1,2,3 as nvarchar and SQL complaining that it can't convert this to an int.
Alter PROCEDURE sp_PagedItems_Listings
(
@Creator nvarchar(80) = null,
@DiciplineID int,
@SpecialtyID nvarchar(500),
@FacilityID int,
@Location nvarchar(500),
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
ListingID int,
ListingTypeID int,
UserID int,
FirstName nvarchar(50),
LastName nvarchar(50)
)
INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName)
Select Distinct Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName
From Listings
Inner Join Users On Users.UserID = Listings.UserID
Inner Join ListingSpecialtyDetails On ListingSpecialtyDetails.Li
Inner Join ListingFacilitiesDetails On ListingFacilitiesDetails.L
Where ListingID IN (Select ListingID From ListingSpecialtyDetails Where SpecialtyID IN (@SpecialtyID))
you can't use in directly against the variable... (only if using a dynamic sql string)
you can do a like comparision if you convert the integer column to character...
.. please tidy up your o/s questions
if you feel they whern't answered correctly then post a comment in the support areas and ask for a refund..
Use VARCHAR not NVARCHAR (see my sample code) unless you really HAVE to use NVARCHAR. NVARCHAR takes twice as many bytes and so is half as efficient :-).
Also, SQL will not interpret the line below the way you want it to:
Where ListingID IN (Select ListingID From ListingSpecialtyDetails Where SpecialtyID IN (@SpecialtyID))
SQL will see the variable as ONE value only, so it will try to translate:
1, 2, 3
into a SINGLE numeric value, which will of course cause an error every time. I think you will have to split the field into a table variable (my example) or temp table (Lowfat's example) to get the results you want.
Please confirm, are you on SQL 2K or SQL 7.0?
>>As for maintaining the open questions, what's the protocal if I never received an acceptible answer? Do I awrd the points and give a bad grade?<<
See here:
http://www.experts-exchang
Anthony
Business Accounts
Answer for Membership
by: mikkilineniPosted on 2003-09-25 at 10:37:55ID: 9430056
assuming SpecialtyID is of Integer Datatype
stingID = Listings.ListingID pecialtyID IN ( ' + @SpecialtyID +') )
Exec('INSERT INTO #TempItems (ListingID, ListingTypeID, UserID, FirstName, LastName)
Select Distinct Listings.ListingID, Listings.ListingTypeID, Listings.UserID, Users.FirstName, Users.LastName
From Listings
Inner Join Users On Users.UserID = Listings.UserID
Inner Join ListingSpecialtyDetails On ListingSpecialtyDetails.Li
Where (ListingSpecialtyDetails.S