How can I pass a parameter to use in an IN

I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.

Dim MySqlParamSelected As New SqlParameter("@.Selected", SqlDbType.Int)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("intSelected")

WHERE tblSelected.Selected_ID IN (@.Selected)

It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.

Here is the error:

Msg 119, Level 15, State 1, Line 4
Must pass parameter number 7 and subsequent parameters as'@.name = value'. After the form'@.name = value' has been used, all subsequent parameters must be passed in the form'@.name = value'.

Does anyone know how I can correctly pass multiple numbers to use in my IN?

Have a look atthis article which discusses this very issue.

|||

Oh, that is a very clever little trick!

|||

I have tried using fn_split function from the artical.

I now get the following error:

Failed to convert parameter value from a String to a Int32.

Does anyone have any Ideas about this?

|||

Did you run the debugger to find out which parameter it was complaining about? What value you were passing in? Whether it should be an Int32 parameter instead of a string parameter?

|||

Do you mean placing break points? I'm not sure how to check for all of the things you listed.

My code_ID data type is int, my parameter is and int, but I'm quessing my session variable is a string. If I'm passing to the parameter '78,79' I don't see how it will ever be and int. This thing has me very confused.

I appreciate any help with this I can get!!!

|||

Your new procedure should take a varchar parameter (string) of id values i.e. '1,2,3,4,5' and that function will split it up. You should'nt be sending an int parameter.

|||

I must be missing something, if I change my parameter to a varchar I get this: Error converting data type varchar to int.

Dim MySqlParamSelectedAs New SqlParameter("@.Selected", SqlDbType.VarChar)
Cmd.Parameters.Add(MySqlParamSelected)
MySqlParamSelected.Value = Session("Selected")

I checked the session varaiable and it is '78,79'

Here is what is in my where

tblTemplates.Template_IDIN(SELECTValueFROM fn_Split(@.Selected,','))

Template_ID is an Int datatype.

What could I be doing wrong?

|||

Ok, I got it. It was my tunnel vision, I was so hung up on looking at the WHERE I didn't see that my @.Selected parameter we declared as an Int.

|||

Just FYI: if you have a few values it might work out okay but if you have hundreds of values it could be a little slow. the IN is internally converted to OR and SQL Server will look for each value in the IN. If you do a JOIN it might be a little faster.

|||

ndinakar,

I must ask, what do you mean by doing a Join?

|||

More like this:

SELECT *FROM Table1 TJOIN (SELECT *FROM dbo.fnGetSomething (@.String,',')) FON F.somecol = T.someothercolWHERE T.something = @.x

rather than this:

SELECT *FROM Table1 TWHERE T.something = @.xAND T.someothercolIN (Select colfrom dbo.fnGetSomething (@.String,',') )