troycomp
asked on
Selecting multiple records from a comma delimited string (SQL Server 2008)
I have to pull 200 records at a time, update 2 fields on those records, then return data from a select statement on those records. I'm stuck on one thing. Heres my code:
DECLARE @OrderList varchar(100)
SELECT top 200 @OrderList = COALESCE(@EmployeeList + ', ', '') +
CAST(SourceOrderId AS varchar(25))
FROM OrderHeader
WHERE isfulfilled = 0 and inprocess=0
Im creating a comma delimited string and thats working. Then I update my records:
declare @Del nvarchar(10)
set @Del = ','
BEGIN
DECLARE @Pos int
DECLARE @Item nvarchar(200)
SET @Pos = CHARINDEX(@Del, @OrderList)
WHILE @Pos > 0
BEGIN
SET @Item = LTRIM(SUBSTRING(@OrderList , 1, @Pos-1))
update OrderHeader set InProcess=1,InProcessDate= GETDATE() where SourceOrderId = @Item
SET @OrderList = STUFF(@OrderList, 1, @Pos+LEN(@Del)-1, '')
SET @Pos = CHARINDEX(@Del, @OrderList)
END
END
But when I run the following:
select * from OrderHeader where SourceOrderId in (@OrderList )
I get the following error:
Conversion failed when converting the varchar value '100000020, 100000021, 100000026, 100000027, 100000028, 100000032, 100000033, 100000034, 100000035, 1' to data type int.
SourceOrderId is of type int not varchar. How can i build a comma delimited string and pass it in to my select statement when the types are different? Is there a way to cast my varchar string or something?
DECLARE @OrderList varchar(100)
SELECT top 200 @OrderList = COALESCE(@EmployeeList + ', ', '') +
CAST(SourceOrderId AS varchar(25))
FROM OrderHeader
WHERE isfulfilled = 0 and inprocess=0
Im creating a comma delimited string and thats working. Then I update my records:
declare @Del nvarchar(10)
set @Del = ','
BEGIN
DECLARE @Pos int
DECLARE @Item nvarchar(200)
SET @Pos = CHARINDEX(@Del, @OrderList)
WHILE @Pos > 0
BEGIN
SET @Item = LTRIM(SUBSTRING(@OrderList
update OrderHeader set InProcess=1,InProcessDate=
SET @OrderList = STUFF(@OrderList, 1, @Pos+LEN(@Del)-1, '')
SET @Pos = CHARINDEX(@Del, @OrderList)
END
END
But when I run the following:
select * from OrderHeader where SourceOrderId in (@OrderList )
I get the following error:
Conversion failed when converting the varchar value '100000020, 100000021, 100000026, 100000027, 100000028, 100000032, 100000033, 100000034, 100000035, 1' to data type int.
SourceOrderId is of type int not varchar. How can i build a comma delimited string and pass it in to my select statement when the types are different? Is there a way to cast my varchar string or something?
I'm not sure why are you creating that comma delimited list, you can just do it like this
select *
from OrderHeader
where SourceOrderId in (SELECT top 200 SourceOrderId FROM OrderHeader WHERE isfulfilled = 0 and inprocess=0))
Anyway, here's your corrected query
select * from OrderHeader where ', ' + @OrderList + ', ' like '%, ' + cast(SourceOrderId as varchar) + ', %
select *
from OrderHeader
where SourceOrderId in (SELECT top 200 SourceOrderId FROM OrderHeader WHERE isfulfilled = 0 and inprocess=0))
Anyway, here's your corrected query
select * from OrderHeader where ', ' + @OrderList + ', ' like '%, ' + cast(SourceOrderId as varchar) + ', %
ASKER
The only thing i saw in that article was where the select statement was filtered to ssn being in a comma delimited list, but ssn is of type varchar cause it has dashes in it. So i dont see how this helps me
the column being named SSN or SourceOrderId, does not matter.
select * from OrderHeader where SourceOrderId in (select value from dbo.ParmsToList(@OrderList,',') )
ASKER
@ralmada
Im creating a comma delimited list because i have to update those 200 records, then run a final select statement to pull data and send it back to the client. But your method worked:
select * from OrderHeader where ', ' + @OrderList + ', ' like '%, ' + cast(SourceOrderId as varchar) + ', %
Can you explain whats actually being done. I like to learn as well as get my answer
Im creating a comma delimited list because i have to update those 200 records, then run a final select statement to pull data and send it back to the client. But your method worked:
select * from OrderHeader where ', ' + @OrderList + ', ' like '%, ' + cast(SourceOrderId as varchar) + ', %
Can you explain whats actually being done. I like to learn as well as get my answer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You're awesome. Thank you so much.
https://www.experts-exchange.com/A_1536.html