sqlParameter + IN( ) clause

  • Thread starter Thread starter VR
  • Start date Start date
V

VR

Hi, I'm trying to execute a statement like

select *
from myTable
where code IN (1,2,4)

to do that I used a StringBuilder to concatenate the string and then, inside
the ( ) I put (@myNumbers)
then, cmd.Parameters.Add(New
sqlParameter("@myNumbers",myStringBuilder.toString))

when I execute the command from a sqlDataAdapter to fill a dataTable WHEN I
HAVE MORE THAN ONE NUMBER I get: Error converting data type nvarchar to
bigint

Do anyone know about it? (My SQL Server column is of type BigInt)


Thanks a lot,
Regards,
Victor Reboucas
 
Hi,

You cannot pass values into IN as a parameters. This is limitation. To
handle this situation I used XML. to pass array of values into SQL Server
200 stored procedure. Here is some sort of example how I did this

CREATE PROCEDURE dbo.usp_PassArray
@INVALUES as VARCHAR(1000)

AS

SET NOCOUNT ON

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @INVALUES

SELECT * FROM tblMyTable WHERE MyFieldName IN (SELECT Value FROM OPENXML
(@hdoc, '/ROOT/HASH',1) WITH (Value varchar(16)))

EXEC sp_xml_removedocument @hdoc

RETURN
 
Val, thanks a lot for the help.

I tryed using the parameters collection because of SQL Injection (by the
way, I solved that just by concatenating strings, but...), will XML have the
same behavior here as a SQL Parameter? (or I have to validate the values by
hand to avoid SQL Injection?)


Thanks a lot,
Victor Reboucas
 
Hi,

Since we do not use any concatenation inside of SP, it will not lead to
injection. Even if someone passes something wrong instead of valid XML
string and you application will not check it, SP will fail on EXEC
sp_xml_preparedocument @hdoc OUTPUT, @INVALUES and nothing will be done.
Before passing XML as a parameter to SP, you could check if it is valid XML
string or not.
 
Back
Top