?
Solved

Use delimited field value as 'IN clause'

Posted on 2004-08-09
9
Medium Priority
?
531 Views
Last Modified: 2008-02-01
I have a table with a field set up as nvarchar (255), an example of data in this field is '4,5,6,7,11,12,13' (without the single quotes) those values match up to keys in a different table. What i need to do (using a stored proceedure) is select the keyed records. This is what i thought would work, but does not:

DECLARE @UID int
SET @UID = 5

SELECT * FROM SKIP_WebSites WHERE SiteID IN (SELECT SkipSites FROM Users WHERE ID = @UID)

The error i get is:
Syntax error converting the nvarchar value '4,5,6,7,11,12,13' to a column of data type int.

I suppose my error was thinking it could be that simple, any help on how to achieve this ?

0
Comment
Question by:odditysoftware
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 11754838
Use execsql:

put your query into a single text value then execute it:

declare @mysql nvarchar(2000)

set @mysql = "SELECT * FROM SKIP_WebSites WHERE SiteID IN (SELECT SkipSites FROM Users WHERE ID = " + @UID+ ")"

exec sp_executesql @mysql


0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 11754877
Ooops - use single quotes there, not double as shown
0
 
LVL 10

Assisted Solution

by:AaronAbend
AaronAbend earned 300 total points
ID: 11754907
Here it is complete and corrected...


DECLARE @UID varchar  -- change uid to a varchar or convert it afterwards
declare @mysql nvarchar(2000)

SET @UID = 5

set @mysql = 'SELECT * FROM SKIP_WebSites WHERE SiteID IN (SELECT SkipSites FROM Users WHERE ID = ' + @UID+ ')'

-- alternative with convert...
--set @mysql = 'SELECT * FROM SKIP_WebSites WHERE SiteID IN (SELECT SkipSites FROM Users WHERE ID = ' + convert(varchar, @UID)+ ')'

exec sp_executesql @mysql
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Accepted Solution

by:
Brendt Hess earned 300 total points
ID: 11754930
Hmmm... No, that's not going to work, because the data is incompatable

The "correct" method to do this would be to have a table containing the SkipSites, e.g.

CREATE TABLE User_SkipSites (ID Int, SiteID int)
Create Index ix_Users on User_SkipSites (ID)

Now, your SELECT would be:

SELECT * FROM SKIP_WebSites
WHERE SiteID IN
    (SELECT SiteID FROM User_SkipSites WHERE ID = @UID)

This allows for an unlimited number of 'skip sites', and is much easier to work with.

If you cannot do this, then you will have to create your SQL Statements on the fly, or create a user defined function to return a table variable containing the parsed out values.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11754933
you declare @uid as an INT, and then try to pass-in a string

SQL Server is not able to convert '4,5,6,7,11,12,13' to a integer value
That's why you get this message

DECLARE @UIDs varchar

then use dynamic SQL as Aaron suggested
his code will work if you change the
 WHERE ID = " + @UID+ ")"
to
 WHERE ID IN (" + @UID+ "))"

0
 
LVL 3

Author Comment

by:odditysoftware
ID: 11755074
I decided to split the poins, after reviewing the answer(s) i got it to work after some tweaking, and merging the ideas together. the solution that worked is:

DECLARE @UID int

DECLARE @mysql nvarchar(2000)
DECLARE @MySites nvarchar(255)

SET @UID = 5
SELECT @MySites = SkipSites FROM users WHERE ID = @UID

set @mysql = 'SELECT * FROM SKIP_WebSites WHERE SiteID IN (' + @MySites+ ')'

exec sp_executesql @mysql
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11755140
agree with bhess on how to normalize this correctly.


But here is a Dirty bit of SQL that'll do it:




DECLARE @UID int
SET @UID = 5

SELECT * FROM SKIP_WebSites WHERE SiteID IN (SELECT SkipSites FROM Users WHERE ID = @UID)


select a.*
  from  SKIP_WebSites  b
       inner join Users a on ','+a.SkipSites+',' like  '%,'+CONVERT(varchar(10),b.SiteID)+',%'  and b.ID=@UID


(Yee ha, ridem cowboy)


0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11755175
oops pasting error

DECLARE @UID int
SET @UID = 5

select a.*
  from  SKIP_WebSites  b
       inner join Users a on ','+a.SkipSites+',' like  '%,'+CONVERT(varchar(10),b.SiteID)+',%'  and b.ID=@UID

0
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 11755237
If you want to keep the data like you have it now, use this function:

CREATE FUNCTION [dbo].[tfn_ParseSkipIDs] (@ID int )  
RETURNS @SkipIDs TABLE
      (
      SkipID int
      ) AS  
BEGIN
      Declare @List nvarchar(256)
      Select @List = SkipSites from Users WHERE ID = @ID
      If Coalesce(@List,'') > ''
      BEGIN
            Declare @Ptr1 int
            Declare @Ptr2 int
            Declare @SkipID int

            Set @List = @List + ','
            Set @Ptr2 = 1
            Set @Ptr1 = charindex(',', @List, @Ptr2)
            WHILE @Ptr1 <= Len(@List)
            BEGIN
                  Set @SkipID = Cast(SubString(@List, @Ptr2, (@Ptr1 - @Ptr2)) As int)
                  Insert Into @SkipIDs (SkipID) Values (@SkipID)
                  Set @Ptr2 = @Ptr1 + 1
                  If @Ptr2 > Len(@List)
                  BEGIN
                        Set @Ptr1 = @Ptr2
                  END
                  Else
                  BEGIN
                        Set @Ptr1 = charindex(',', @List, @Ptr2)
                  END
            END
      END
RETURN

END

Like this:

SELECT * From Skip_Websites
Where SiteID In
    (SELECT SkipID From tfn_ParseSkipIDs(@ID))
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question