Post reply

Passing parameters to a Where In statement

  • August 26, 2008 at 12:43 am

    #196923

    I have a stored procedure that uses a "Where InvoiceID in (16,17,18)" construct.

    If I define the input parameter to the stored procedure as nvarchar (which is what I would do if running a query in VB), then I get the following error - "Conversion failed when converting the nvarchar value '16,17,18' to data type int."

    How do I define an input parameter that will allow me to pass an array of integers?

    Many thanks

  • August 26, 2008 at 1:31 am

    #862946

    There is a very beutiful article for the problem that you are talking about. Below is the link:

    http://www.sommarskog.se/arrays-in-sql-2005.html

  • August 26, 2008 at 2:41 am

    #862964

    Solved problem myself. Thanks

  • August 26, 2008 at 10:13 am

    #863254

    rdavid (8/26/2008)

    I have a stored procedure that uses a "Where InvoiceID in (16,17,18)" construct.

    If I define the input parameter to the stored procedure as nvarchar (which is what I would do if running a query in VB), then I get the following error - "Conversion failed when converting the nvarchar value '16,17,18' to data type int."

    How do I define an input parameter that will allow me to pass an array of integers?

    Many thanks

    remember also "IN" is ok where you are garaunteed only a few items to compare but when a large group is used get the habit of "where exists" 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply