Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Use delimited field value as 'IN clause'

Posted on 2004-08-09
9
Medium Priority
?
527 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
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 ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

618 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