How to send a comma delimited string of integers as a parameter list in sql server?

Question

In this sql server stored procedure query

SELECT HireResponseID,
       HireResponse,
       DateResponse,
       Comments,
       YearFileOpened,
       file_number,
       isCaseOpen,
       last_update,
       isConfidential,
       date_created,
       OurClient,
       TheirClient,
       ProjectName,
       description,
       lawyer_lastname,
       lawyer_firstname,
       Conflicts.ConflictID
FROM Hire_Response,
     Conflicts,
     Lawyers
WHERE Hire_Response.ConflictID=Conflicts.ConflictID
  AND Lawyers.lawyerID=Conflicts.lawyerID
  AND firmID = @FirmID
  AND HireID IN @HireID
  AND isStillaConflict = 1
ORDER BY file_number,
         TheirClient,
         OurClient,
         lawyer_lastname,
         lawyer_firstname

The parameter @HireID is a string of comma delimited integers (it doesn't have brackets around it). I want to check if the HireID integer is in the @HireID string. But I don't know how to parse this.

Can anyone help please?

Thanks

Instead of the useless microsoft tag please specify your specific version of SQL Server. — Martin Smith, May 28 '13 at 20:46
possible duplicate of [SQL Server Split operation](http://stackoverflow.com/questions/2507330/sql-server-split-operation) — Martin Smith, May 28 '13 at 20:51

Hart CO · Answer 1 · 2013-05-28T21:04:29.810

0

If I understand your question, you want to find rows where HireID is in the list @HireID. If the HireID is a consistent length, and the list is delimited, then you could use this:

AND @HireID LIKE '%'+CAST(HireID AS VARCHAR(5))+'%'

You could also use CHARINDEX:

AND CHARINDEX(HireID,@HireID) > 0

Edit: To account for inconsistent length, you could use:

AND (@HireID LIKE '%'+CAST(HireID AS VARCHAR(5))+',%'
    OR @HireID LIKE '%,'+CAST(HireID AS VARCHAR(5))+'%')

edited May 28 '13 at 21:04

answered May 28 '13 at 20:53

Hart CO

34,064
6
48
63

char index could fail for this though 1 in "11,12" Could I do something like CHARINDEX(string(HireID) + ",", @HireID + ",") > 0 ? – omega May 28 '13 at 20:57
The @HireID already has comma's, right? I was imagining '1244,1245,1246' or some such. The problem with using the comma is that the last in the list won't have a trailing comma, so you'd have to require a trailing comma, or a leading comma. – Hart CO May 28 '13 at 21:02
It has comma's, but only in between numbers. – omega May 28 '13 at 21:07
The updated answer should account for this, requiring either a comma before or comma after the string. – Hart CO May 28 '13 at 21:23

score 0 · Answer 2 · answered May 29 '13 at 05:39

Try this one -

DECLARE @HireID VARCHAR(100)
SELECT @HireID = '2,18'

;WITH cte AS 
(
    SELECT ID = t.c.value('.', 'INT')
    FROM (
        SELECT txml = CAST('<t>' + REPLACE(@HireID, ',', '</t><t>') + '</t>' AS XML)
    ) a
    CROSS APPLY txml.nodes('/t') AS t(c)
)
SELECT *
FROM Hire_Response
JOIN Conflicts ON Hire_Response.ConflictID = Conflicts.ConflictID
JOIN Lawyers ON Lawyers.lawyerID = Conflicts.lawyerID
WHERE firmID = @FirmID
    AND isStillaConflict = 1
    AND HireID in (SELECT ID FROM cte) 
ORDER BY file_number, TheirClient, OurClient, lawyer_lastname, lawyer_firstname

How to send a comma delimited string of integers as a parameter list in sql server?

2 Answers2