Troubles with IN instruction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Troubles with IN instruction

Author

Topic

celene
Starting Member

1 Post

Posted - 2003-01-20 : 13:48:04

declare @x varchar(50)
set @x='1,2,3'
select * from machines where machine_id in (@x)
Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value '1,2,3' to a column of data type int.
select * from machines where machine_id in (1,2,3) "OK"

What I can do to execute the first query well

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-20 : 14:27:32

http://www.sqlteam.com/redir.asp?ItemID=10728

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

http://www.sqlteam.com/item.asp?ItemID=11499

Edited by - ValterBorges on 01/20/2003 16:52:17

zipman1952
Starting Member

14 Posts

Posted - 2003-01-20 : 14:38:50

declare @x varchar(50), @sql varchar (100)
set @x='1,2,3'
select @sql ='select * from machines where machine_id in (' + @x + ')'
exec (@sql)

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2003-01-20 : 23:34:10

hi,
why do u want to use dynamic sql ?
u can use this function to convert the csv to int.

I had found the following function on this site you can use this:

Create Function dbo.CsvToInt ( @Array varchar(1000))
returns @IntTable table
(IntValue int)
AS
begin

declare @separator char(1)
set @separator = ','

declare @separator_position int
declare @array_value varchar(1000)

set @array = @array + ','

while patindex('%,%' , @array) <> 0
begin

select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)

Insert @IntTable
Values (Cast(@array_value as int))

select @array = stuff(@array, 1, @separator_position, '')
end

return
end

or make a search for csv on this site there is plenty of info.

Regards,
harshal

Expect the UnExpected

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources