May 25, 2006 at 6:32 am
I get this error:
"Conversion failed when converting the varchar value '123, 456' to data type int."
When I run this script:
SELECT * FROM Users WHERE ID_User
IN (SELECT ID_User_List FROM MyUsers)
"ID_User_List" filed is Varchar type that contains list of ID's that sepereated by comma, like: 222,555,777,888
How to solve it?
May 25, 2006 at 7:05 am
Sharon
You need to normalise the MyUsers table. This means having one user ID in each row of the table. Then your query will look like this:
SELECT u.col1, u.col2, m.col1, m.col2
FROM Users u INNER JOIN MyUsers m
ON u.ID_User = m.ID_User_List
If you really want to persist with your current structure, try this (untested):
DECLARE @UserList varchar(500) --choose a size here to suit the length of your list
SET @UserList = (SELECT ID_User_List FROM MyUsers)
SELECT * FROM Users
WHERE CHARINDEX (CAST (ID_User AS varchar(6), @UserList) > 0
But beware. The performance of this will be terrible if your table is large since no index will be used. Also, you'll need to tweak this if:
(i) Your user IDs aren't all three-figure numbers
(ii) There is more than one row in MyUsers
John
May 25, 2006 at 7:37 am
You can also use dynamic tsql to solve the issue:
DECLARE @SQL nvarchar(4000)
SET @SQL='
SELECT * FROM Users WHERE ID_User
IN ('+(SELECT TOP 1 ID_User_List FROM MyUsers)+')'
EXEC (@SQL)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply