Sub select or charindex?

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.

Yes....

Rather than using Dynamic SQL (performance and security problems) consider parsing the delimitted string into a temporary table or (if you are using SQL 2K) use XML to pass in the values.

Anthony

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.ListingID = Listings.ListingID
Inner Join ListingFacilitiesDetails On ListingFacilitiesDetails.ListingID = Listings.ListingID
Where (ListingSpecialtyDetails.SpecialtyID IN (' + @SpecialtyID + ') OR @SpecialtyID is Null)

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

ACperkins,
Thanks. I would prefer to not use a dynamic query. My knowledge of XML sucks - but how to I go about doing a temp table?

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?

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.

Just saw Lowfat's, pretty similar :-)

Note that both avoid use of dynamic SQL, which should be used when absolutely necessary.

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.ListingID = Listings.ListingID
Inner Join ListingFacilitiesDetails On ListingFacilitiesDetails.ListingID = Listings.ListingID
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?

Thanks guys,
I appreciate the help. I am on SQL 2000.