MarkNethercott
asked on
Passing array of values to SQL stored procedure
I have a stored procedure (the type of @MachineSet is probably wrong - it's there from my testing);
PROCEDURE dbo.MachineSelect
(@MachineSet varchar(250))
AS
BEGIN
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (@MachineSet)
END
And I want to pass an array of values to @MachineSet. Say I was looking for machines with IDs of 1,17,23,56 the query should look like the following
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (1,17,23,56 )
Though it could just as easily be
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (1 )
If I was only looking for MachineID=1
My VB code sets up a parameter @MachineSet,
Dim da As New SqlClient.SqlDataAdapter
Dim dbTable As String = "tblMachine"
Dim dbStoredProcedure As String = "MachineSelect"
da.SelectCommand = New SqlClient.SqlCommand(dbSto
da.SelectCommand.CommandTy
Dim pc As SqlClient.SqlParameterColl
pc.Add("@MachineSet", SqlDbType.Int, 4)
pc("@MachineSet").Value = MachineSet
(1) What SQL type am I trying to pass? What's the variable type of the (SET) int or varchar?
(2) How do I set up the parameter to correctly pass the 'array' of MachineIDs?
PROCEDURE dbo.MachineSelect
(@MachineSet varchar(250))
AS
BEGIN
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (@MachineSet)
END
And I want to pass an array of values to @MachineSet. Say I was looking for machines with IDs of 1,17,23,56 the query should look like the following
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (1,17,23,56 )
Though it could just as easily be
SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData
FROM tblMachine
WHERE MachineID IN (1 )
If I was only looking for MachineID=1
My VB code sets up a parameter @MachineSet,
Dim da As New SqlClient.SqlDataAdapter
Dim dbTable As String = "tblMachine"
Dim dbStoredProcedure As String = "MachineSelect"
da.SelectCommand = New SqlClient.SqlCommand(dbSto
da.SelectCommand.CommandTy
Dim pc As SqlClient.SqlParameterColl
pc.Add("@MachineSet", SqlDbType.Int, 4)
pc("@MachineSet").Value = MachineSet
(1) What SQL type am I trying to pass? What's the variable type of the (SET) int or varchar?
(2) How do I set up the parameter to correctly pass the 'array' of MachineIDs?
It won't work this way. You cannot pass a parameter for IN operator.
You could try dynamic SQL using string concatenation and EXEC statement:
DECLARE SqlStr varchar(1000)
SET SqlStr = 'SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData FROM tblMachine WHERE MachineID IN (' + @MachineSet + ')'
EXEC (SqlStr)
Or process your parameter in loop, insert all values into temporary table and build your query using JOIN with that table, instead of IN.
You could try dynamic SQL using string concatenation and EXEC statement:
DECLARE SqlStr varchar(1000)
SET SqlStr = 'SELECT MachineID, NameFull, NameAbbreviated, isOptimizerData, isApplicationManagerData FROM tblMachine WHERE MachineID IN (' + @MachineSet + ')'
EXEC (SqlStr)
Or process your parameter in loop, insert all values into temporary table and build your query using JOIN with that table, instead of IN.
in this code:
Dim pc As SqlClient.SqlParameterColl
pc.Add("@MachineSet", SqlDbType.Int, 4)
pc("@MachineSet").Value = MachineSet
you are attempting to pass an INTEGER value, to a parameter that is expecting a STRING (varchar(250) means a string of UP TO 250 characters).
However, you have a MUCH more serious problem, because the way you have your SP coded, it will FAIL whenever you attempt to pass a list of IDs. The LIST in being passed as a STRING, and your SP is attempting to convert that STRING into an INTEGER. If you attempt to pass the list as "1,2,3", the SP fails attempting to convert the STRING "1,2,3", into a SINGLE INTEGER. If you pass a SINGLE integer ('1' for instance) the SP will work, because it can convert '1' into the INTEGER value 1, and that is proper.
AW
Dim pc As SqlClient.SqlParameterColl
pc.Add("@MachineSet", SqlDbType.Int, 4)
pc("@MachineSet").Value = MachineSet
you are attempting to pass an INTEGER value, to a parameter that is expecting a STRING (varchar(250) means a string of UP TO 250 characters).
However, you have a MUCH more serious problem, because the way you have your SP coded, it will FAIL whenever you attempt to pass a list of IDs. The LIST in being passed as a STRING, and your SP is attempting to convert that STRING into an INTEGER. If you attempt to pass the list as "1,2,3", the SP fails attempting to convert the STRING "1,2,3", into a SINGLE INTEGER. If you pass a SINGLE integer ('1' for instance) the SP will work, because it can convert '1' into the INTEGER value 1, and that is proper.
AW
ASKER
I can make MachineSet whatever's needed. The fact that @MachineSet is currently varchar might be a bit of a red herring.
I've tried passing a string (eg. "1,17,23,56") to @MachineSet, but the query fails.
I can't figure out;
a) what type of 'object' the SQL parameter (in the SQL stored procedure) needs to be so that when it goes in to the WHERE ... IN (@Parameter) statement it gets interpretted correctly. Maybe, I'm using the wrong syntax to for the WHERE .... IN () command if you're passing it a parameter.
b) if it's NOT a string, how to pass that to the SelectCommand.Parameter.
My expectation was that a string of 'values' would work, but it fails. Somehow, the SQL parameter needs to be a 'string' of integers separated by ','s but without the enclosing " " of a string (if that makes sense)
I've tried passing a string (eg. "1,17,23,56") to @MachineSet, but the query fails.
I can't figure out;
a) what type of 'object' the SQL parameter (in the SQL stored procedure) needs to be so that when it goes in to the WHERE ... IN (@Parameter) statement it gets interpretted correctly. Maybe, I'm using the wrong syntax to for the WHERE .... IN () command if you're passing it a parameter.
b) if it's NOT a string, how to pass that to the SelectCommand.Parameter.
My expectation was that a string of 'values' would work, but it fails. Somehow, the SQL parameter needs to be a 'string' of integers separated by ','s but without the enclosing " " of a string (if that makes sense)
ASKER
Hmmm, thanks chaniewskim - if (as you say) you can't pass a parameter to an IN operator, then I'll check out the alternatives you've suggested
Hey, it really can't work this way.
IN accepts a SQL statement - nested SELECT or a list of values separated by commas. You are trying to pass a varchar variable there. It won't work, it is like you were trying to do SELECT @MyFields FROM MyTable - it won't work. SQL statements cannot be assembled from strings, unless you prepare whole statement as string and pass it to EXEC - as I suggested above.
IN accepts a SQL statement - nested SELECT or a list of values separated by commas. You are trying to pass a varchar variable there. It won't work, it is like you were trying to do SELECT @MyFields FROM MyTable - it won't work. SQL statements cannot be assembled from strings, unless you prepare whole statement as string and pass it to EXEC - as I suggested above.
ASKER
Thanks Arthur for your response, I knew that there was a bit of a mis-match in my 'variable' types. My poblem was how to get a 'parameter' in to the IN operator - what type was SQL expecting & if SQL needed an array, how to construct that from the .NET end. I think that chaniewskim
may have identified the 'issue'.
I'm off to read up on dynamic SQL :-)
may have identified the 'issue'.
I'm off to read up on dynamic SQL :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Bob