Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want the equivalent of this for ints.

--Valid SQL
DECLARE @users AS nvarchar(50)
SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23
select * FROM Person AS p
WHERE CHARINDEX(p.sFullName,@users) > 0

So something like this.

--Invalid SQL
DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
select * FROM Person AS p
WHERE p.ixPerson IN(@users
share|improve this question
3  
Neither example is valid syntax... –  Jon Seigel Aug 8 '12 at 15:12
    
I did not think I had to put a Select * From table to show my point. I have updated the question with valid SQL. –  joe Aug 9 '12 at 17:03
    
Pure t-sql or are you using any front-end code? –  swasheck Aug 9 '12 at 17:22
add comment

6 Answers

up vote 4 down vote accepted

You haven't really indicated how this is called. Do you really have a string full of comma separated ints or is that just an artifact of some other aspect of your system?

If the outer part of the environment around this requirement is changeable, then typically in SQL Server 2008 and up, I pass a table-valued parameter containing a list of IDs (perhaps from ADO.NET or whatever) and then just use it as a table inside the procedure - i.e. WHERE value IN (SELECT id FROM @table) or INNER JOIN @table AS t ON t.id = users.t

share|improve this answer
add comment

I'd look at a numbers table type solution to split that delimited data into something you can work with http://stackoverflow.com/questions/8956671/how-to-convert-varchar-to-integer-in-sql-server-stored-procedure/8958460#8958460 Then you can use the IN clause just fine

share|improve this answer
add comment

I don't love this solution, but this is what I did.

DECLARE @users AS nvarchar(MAX)
SET @users = ('8,10,28')

DECLARE @finalCommand AS NVARCHAR(MAX)
SET @finalCommand = 'SELECT p.sFullName, *
                 FROM   Person as p
                 WHERE  p.ixPerson IN (' +  @users + ')'

EXEC sp_executesql  @finalCommand 
share|improve this answer
add comment

Option 1 (first part ripped directly from Adam Machanic's Blog)

DECLARE @number_of_numbers INT = 100000;
;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS
(
SELECT TOP(@number_of_numbers)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
FROM f
)
SELECT [your],[required],[columns],[here]
FROM Person where Person.ixPerson in (select number from numbers);

Of course, this is just one way to create a numbers table. If you're looking for a random assortment of ids, you can populate a numbers table real quick by using a table variable or something. But then you'd have to separate the list in your code or with a UDF as billinkc proposed at which point in time it might just be better to construct the IN clause.

Option 2 (using the answer by @CadeRoux here):

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  );

declare @nums nvarchar(100);
set @nums = N'1,2,5,10,87';
SELECT [your],[required],[columns],[here]
    FROM Person where Person.ixPerson in (select CAST(s AS INTEGER) s from dbo.Split(N',',@nums));
share|improve this answer
add comment

I expect this simple solution would work fine in many cases although it doesn't particularly scale (but neither does the example in your question using CHARINDEX):

DECLARE @users AS nvarchar(50)
SET @users = '1,2,8,23'
SELECT *
FROM Person AS p
WHERE ',' + @users + ',' LIKE '%,' + CAST(VARCHAR(10), p.ixPerson) + ',%'
share|improve this answer
add comment
DECLARE @IDs AS nvarchar(50);
SET @IDs = N'1,2,8,23';

SELECT p.*
FROM dbo.Person AS p
WHERE CHARINDEX(N',' + CONVERT(nvarchar(11), p.ID) + N',', N',' + @IDs + N',') > 0;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.