Solved

Use delimited field value as 'IN clause'

Posted on 2004-08-09
9
512 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 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now