Passing a string of concatenated strings to be used with ''IN'' operator in the query

Question

Post reply

Passing a string of concatenated strings to be used with ''IN'' operator in the query

Sameer S

Valued Member

Points: 67
More actions
July 11, 2006 at 11:35 am

#169590

hi,
I have a situation where i need to pass concatenated string ex:
'002050053','002050090','002050128','002050196','002050199','002050249','002051018','002051020','002051023','002051076'
which want to pass to the stored procedure so as to use it with 'IN' operator in the query. I'm formatting this this string in VB 6.0 . In front end i format it as
vstrDPCI = Chr(34) & "'002050053','002050090','002050128','002050196','002050199','002050249','002051018','002051020','002051023','002051076'" & Chr(34)
and pass it to the Stored procedure. This works fine as long as the lenght is less than 128, if it exceeds 128 i get this error
[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with ''001000001','002050053','002050090','002050128','002050196','002050199','002050249','002051018','002051020','002051023','0020510' is too long. Maximum length is 128.
In the stored procedure this parameter is defined as varchar(8000).
Can anyone help me out..

Viewing 5 posts - 1 through 5 (of 5 total)

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

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 1

How do you pass the string to the stored procedure? Via ADO Command Object?

N 56°04'39.16"
E 12°55'05.25"

Sameer S Valued Member Points: 67 More actions · Answer 2

yes,

this is how i do it

vstrSQL = "PS_SEARCH_MASTER_PRODUCT_TEST " & vstrDPCI

robjRS.CursorLocation = adUseClient

robjRS.Open vstrSQL, cnRun.ConnectionString, adOpenStatic, adLockReadOnly

vstrDPCI is the input parameter & PS_SEARCH_MASTER_PRODUCT_TEST is the stored procedure name and robjRS is ADO recordset

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

First learn about arrays and list queries at http://sommarskog.se/arrays-in-sql.html and then realize that you are sending a lot of parameters to the stored procedure, instead of the intended array.

Remove all ' and put them only in front and after complete string, as this

vstrDPCI = "'001000001,002050053,002050090,002050128,002050196,002050199,002050249,002051018,002051020,002051023,0020510'"

vstrSQL = "PS_SEARCH_MASTER_PRODUCT_TEST " & vstrDPCI

robjRS.CursorLocation = adUseClient

robjRS.Open vstrSQL, cnRun.ConnectionString, adOpenStatic, adLockReadOnly

N 56°04'39.16"
E 12°55'05.25"

Sameer S Valued Member Points: 67 More actions · Answer 4

Thanks Peter ..your hint and some additional stuff has helped me to work it out..