[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

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?

0
troycomp
Asked:
troycomp
  • 3
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to read this article?
http://www.experts-exchange.com/A_1536.html
0
 
ralmadaCommented:
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) + ', %

0
 
troycompAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
troycompAuthor Commented:
@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
0
 
ralmadaCommented:
Sure,

basically the query is using the @Orderlist variable you've created and compare it to the SourceOrderID column in your table using the like operator. Because in SourceOrderID you have only one value then you need to add the '%' characters both at the beginning and at the end, so for example say @OrderList results in

@OrderList = '100000020, 100000021, 100000026' then the query can be translated to

select * from OrderHeader where ', ' + '100000020, 100000021, 100000026' + ', ' like '%, ' + cast(SourceOrderId as varchar) + ', %

and for one row say that SourceOrderID is 100000026 then it translates to:

select * from OrderHeader where ', 100000020, 100000021, 100000026, ' like '%, 100000026, %'

Oh and we are doing the cast to varchar in there because we are adding some commas (, 100000026, )

Hope this helps
0
 
troycompAuthor Commented:
You're awesome. Thank you so much.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now