using a variable to send parameters to IN clause

Question

I have a stored procedure. In this procedure there's a piece of code that look like

...WHERE someParam IN (1,2)...

I should abstract this part, cause an undefined numer of parameters should be considered (not only 1 OR 2). I get this parameter list from vb.net code as a cvs string (i.e. "1,2" or "78, 109" and so on). Pratically my situation will be something like that:

DECLARE @IdParam varchar(100)
SET @IdParam = '1,2'
...
...WHERE someParam IN (@IdParm)...

but this code will clearly produce me an error:

Conversion failed when converting the varchar value '1,2' to data type int.

What can I do to reach my goal and keep SQL engine quiet? If an optimal solution exist I could consider to modify prexisten VB code.

Edit 1: I wouldn't use that ugly solution of the linked post!

You will need to use a function that split the string into table variable and use that table variable in the query — Yuriy Galanter, May 31 '13 at 14:59
@FabianBigler What is this based on? According to the SQLCAT team, TVP is the fastest way to move data into SQL Server from a Client App. — RBarryYoung, May 31 '13 at 15:02
I tested this a while back. But frankly, I would need to doublecheck, if this applied to all scenarios. Jeff also confirms this in the related link "we tested this and table valued parameters are DOG slow. It is literally faster to execute 5 queries than it is to do one TVP." — Fabian Bigler, May 31 '13 at 15:08
TVP is certainly an alternative, but the OP said he is getting a comma delimited string, so that influenced my answer. — dazedandconfused, May 31 '13 at 15:09
I'm ready to change the visual basic code if a better solution exist... — BAD_SEED, May 31 '13 at 15:12
@FabianBigler With all due respect to Jeff, without any code to validate that statement against, I'm going to have to go with the SQLCAT team, given that they hold the world's record for Insert speed on any DBMS. — RBarryYoung, Jun 01 '13 at 15:35
@RBarryYoung sure thing. maybe there is no better option at the moment for this scenario. — Fabian Bigler, Jun 01 '13 at 23:02

score 3 · Answer 1 · answered May 31 '13 at 15:00

3

Use a table valued parameter to send in multiple values.

CREATE PROC foo
   @IDList SomeType READONLY
AS
...
    WHERE someParam IN (SELECT ID FROM @IDList )
...
GO

answered May 31 '13 at 15:00

gbn

422,506
82
585
676

I have SQL SERVER 2005 – BAD_SEED May 31 '13 at 15:17

score 2 · Answer 2 · answered May 31 '13 at 14:59

You can create a function to turn the string into a list...

ALTER FUNCTION [dbo].[fn_MVParamToInt](@RepParam nvarchar(max), @Delim char(1)= ',')
RETURNS @VALUES TABLE (Param int)AS
   BEGIN
   DECLARE @chrind INT
   DECLARE @Piece nvarchar(max)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam,@chrind - 1)
         ELSE
            SELECT @Piece = @RepParam

         IF ISNUMERIC(@Piece) = 1
            INSERT @VALUES(Param) VALUES(CAST(@Piece AS int))

         SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
         IF LEN(@RepParam) = 0 BREAK
      END
   RETURN
END

Then your query becomes something like...

...WHERE mycolumn IN (SELECT Param FROM dbo.fn_MVParamToInt(yourstring, '.'))

using a variable to send parameters to IN clause

2 Answers2