Link to home
Start Free TrialLog in
Avatar of troycomp
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?

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

you want to read this article?
https://www.experts-exchange.com/A_1536.html
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) + ', %

Avatar of troycomp
troycomp

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,',') )

Open in new window

@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
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You're awesome. Thank you so much.