Solved

Use delimited field value as 'IN clause'

Posted on 2004-08-09
9
515 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 39
SQL Distinct Question 3 15
SQL syntax question 6 44
When else statements to move fields 2 40
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

726 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