Filter an ID Column against a range of values

I have the following SQL:

SELECT ',' + LTRIM(RTRIM(CAST(vessel_is_id as CHAR(2)))) + ',' AS 'Id'
FROM Vessels
WHERE ',' + LTRIM(RTRIM(CAST(vessel_is_id as varCHAR(2)))) + ',' IN (',1,2,3,4,5,6,')

Basically, I want to filter the vessel_is_id against a variable list of integer values (which is passed in as a varchar into 开发者_运维知识库the stored proc). Now, the above SQL does not work. I do have rows in the table with a `vessel__is_id' of 1, but they are not returned.

Can someone suggest a better approach to this for me? Or, if the above is OK

EDIT:

Sample data

| vessel_is_id |
| ------------ |
|      1       |
|      2       |
|      5       |
|      3       |
|      1       |
|      1       |

So I want to returned all of the above where vessel_is_id is in a variable filter i.e. '1,3' - which should return 4 records.

Cheers. Jas.

IF OBJECT_ID(N'dbo.fn_ArrayToTable',N'FN') IS NOT NULL
    DROP FUNCTION [dbo].[fn_ArrayToTable]
GO
CREATE FUNCTION [dbo].fn_ArrayToTable (@array VARCHAR(MAX))
-- =============================================
-- Author:      Dan Andrews
-- Create date: 04/11/11
-- Description: String to Tabled-Valued Function
--
-- =============================================
RETURNS @output TABLE (data VARCHAR(256))
AS 
BEGIN

    DECLARE @pointer INT
    SET @pointer = CHARINDEX(',', @array)

    WHILE @pointer != 0
    BEGIN
        INSERT INTO @output
        SELECT RTRIM(LTRIM(LEFT(@array,@pointer-1)))

        SELECT  @array = RIGHT(@array, LEN(@array)-@pointer), 
                @pointer = CHARINDEX(',', @array)
    END

    RETURN
END

Which you may apply like:

SELECT * FROM dbo.fn_ArrayToTable('2,3,4,5,2,2')

and in your case:

SELECT  LTRIM(RTRIM(CAST(vessel_is_id AS CHAR(2)))) AS 'Id'      
FROM Vessels      
WHERE    LTRIM(RTRIM(CAST(vessel_is_id AS VARCHAR(2)))) IN (SELECT data FROM dbo.fn_ArrayToTable('1,2,3,4,5,6')

Since Sql server doesn't have an Array you may want to consider passing in a set of values as an XML type. You can then turn the XML type into a relation and join on it. Drawing on the time-tested pubs database for example. Of course you're client may or may not have an easy time generating the XML for the parameter value, but this approach is safe from sql-injection which most "comma seperated" value approaches are not.

declare @stateSelector xml
set @stateSelector = '<values>
<value>or</value>
<value>ut</value>
<value>tn</value>
</values>'

select * from authors 
where state in ( select c.value('.', 'varchar(2)') from @stateSelector.nodes('//value') as t(c))