Passing in SQL Server Parameter in Where IN clause

By far the best solution is to parse @p into a table variable / temp table and then join on that.  This will produce a far better execution plan [SQL is optimized for doing joins] and the overheading of splitting a char string is relatively low.

DECLARE @recIds TABLE (
    recId INT,
    PRIMARY KEY (recId)  --will help performance!
    )

-- add code here to parse @p into the entries in the table below:
-- there are several examples on EE and on the web in general;
-- if you would like code added to do the split, just let me know.

-- ...code to parse / split goes here
-- ...

select *
from uvwRefill
inner join @recIds recIds ON recIds.recId = uvwRefill.MSRecID

you should avoid the dynamic sql olutions suggested above...

please create the following function:

CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
returns @result TABLE (Value varchar(30))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
     SET @Pos = CHARINDEX(',', @Parameters, 1)

     IF REPLACE(@Parameters, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    


and you will make your procedure like this:

create procedure uspGetRefills
@p varchar(255)
as
select * from uvwRefill
where MSRecID  in (select cast(value as int) from dbo.ParmsToList(@p)  )

How many rows are in uvwRefill?  If/as it becomes progressively bigger, your performance will get progressively worse.

There are 8200 rows in the view, but I will only be returning 5 or 6 at the most.

See you in a few months, when it grows and you post another q to figure out how to speed it up :-) (and we wonder why software development is moving offshore).

Not if I use your solution!!

This function will transform your delimited data into columns

drop function [dbo].[DelimitedToTable]
go
create function [dbo].[DelimitedToTable](@DelimitedString nvarchar(max), @Delimiter nvarchar(32))
returns @Values TABLE
     (ident         int not null identity primary key clustered
     ,theValue      nvarchar(1000)
     )
as
begin
declare    @e  int
          ,@i  int
set @i=1
set @e=1
 
set @DelimitedString=@DelimitedString+@Delimiter
 
while @e<>0 and @i<10
begin
     set @e=charindex(@Delimiter,@DelimitedString)
     if @e<>0
     begin
          insert into @Values (theValue)
          select substring(@DelimitedString,1,@e-1)
          set @DelimitedString = right(@DelimitedString, len(@DelimitedString)-@e)
     end
set @i=@i+1
end
return
end
GO
 
select theValue from [dbo].[DelimitedToTable]('a|basdf|c|d','|')
 
--Then to call it
 
 
select * from uvwRefill
where Cast(MSRecID as varchar(255)) in (select theValue from [dbo].[DelimitedToTable] (@p,','))
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:

....or you could go for a much, much simpler approach

the money shot is the LIKE in the WHERE CLAUSE


CREATE PROCEDURE GET_ROWS
      @DelimitedList varchar(max),
        @Delimiter varchar(30),
AS
BEGIN
            SET @DelimitedList = @Delimiter + @DelimitedList + @Delimiter
            SELECT
                        FIELDX, et_al
            FROM
                        MyTable
            WHERE
                        (@DelimitedList LIKE '%' + @delimiter + FIELDX+ @delimeter + '%')
END

-- SEE WHAT I DID THERE?
-- I made the delimited list as the value to test with a LIKE clause,
-- and made the field the pattern to test
-- instead of the usual other way around

-- so if you have a row where FIELDX = 'XKCD'
-- this will be your test
-- '[XKCD|122317|107509|' LIKE '%|XKCD|%'

-- which is true.


-- then to call the code (probably would do this from app layer, but in sql for this discussion)

EXEC GET_ROWS
      @DelimitedList ='XKCD|122317|107509',
        @Delimiter = '|'



FOR THE RECORD, SPEEDWISE, IT PRETTY MUCH ROCKS

while your solution will scale similar in this case since they are doing a CAST of the searched column, mine will be faster when run against an indexed column since it will be doing equality searches and not double sided wildcard searches.

@groggyjava: lol, "money shot".

@BrandonGalderis: Thank you!!

Here is my version
I have modified the function so that I  always use delimiter ',' and have the returned value in the table as type int.
I have also renamed the function for my own convention. I have renamed the "theValue" field and I have change the input param to varchar(max)

IF OBJECT_ID(N'fn_delimitedtotable', N'TF') IS NOT NULL
	DROP FUNCTION fn_delimitedtotable;
GO

create function [dbo].[fn_delimitedtotable](@DelimitedString varchar(max))
returns @Values TABLE
(
	id int not null identity primary key clustered, 
	v int
)
as
begin
	declare @e int, @i int
	set @i = 1
	set @e = 1

	set @DelimitedString = @DelimitedString + ','
 
	while @e <> 0 and @i < 10
	begin
		 set @e = charindex(',', @DelimitedString)
		 if @e <> 0
		 begin
			  insert into @Values (v)
			  select substring(@DelimitedString, 1, @e - 1)
			  set @DelimitedString = right(@DelimitedString, len(@DelimitedString) - @e)
		 end
		set @i = @i + 1
	end
	return
end
GO

select v from [dbo].[fn_delimitedtotable]('1,2,3,4,5,6')

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34: