How to pass parameter for bigint field with multiple values seperated by comma to query SQLSERVER 2008 R2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

hi,

How to pass parameter for bigint field with multiple values seperated by comma .

I have a Field INVENTORYID with dataType bigint. I need to pass the value '99,100,101' as a parameter to query

SELECT INVENTORY_ID,QUANTITY FROM TRADE_INVENTORY WHERE INVENTORY_ID IN(@INVENTORYID)

Excecuting query I am getting an error as cannot cast varchar to bigint.

How to solve it.

Please Help me..

Thanks & Regards,

Vineetha.K.R

Posted 27-Aug-14 19:34pm

Vineetha Ravindranath

Add a Solution

3 solutions

Solution 1

Here is an article that offers variety of methods:

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

Posted 27-Aug-14 19:56pm

Sanchayeeta

Solution 2

Check helping link.

Passing a varchar full of comma

Convert Comma Delimited

Convert Comma Separated String

Posted 27-Aug-14 19:57pm

MuhammadUSman1

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Accepted Answer · 2014-08-27T20:12:00

Solution 3

Hi,

Try this...

SQL

SELECT INVENTORY_ID,QUANTITY FROM TRADE_INVENTORY 
WHERE INVENTORY_ID IN(
                        SELECT * FROM split(@INVENTORYID,',')
                     )

You can get this Split Function[^] here.

Hope this will help you.

Cheers

Posted 27-Aug-14 20:12pm

Magic Wonder