Solved

Use delimited field value as 'IN clause'

Posted on 2004-08-09
9
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 32

Accepted Solution

by:
bhess1 earned 100 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:bhess1
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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