Passing a List/Array Parameter to Procedure

Question

Passing a List/Array Parameter to Procedure

Rofty

SSCrazy

Points: 2585
More actions
January 7, 2010 at 3:19 am

#228890

I'm writing an app in VBA that connects to SQL2K5 database.
The VBA app calls SQL procedures with parameters to retrieve records that will be loaded into Excel.
I have the need to sent SQL a comma delimited string of GUIDs that the procedure will then use to retrieve data using IN statement.
VBA: "'{B4786611-65F1-46E0-9DB3-C0F30DCDBC0E}','{7EE468EF-1B90-4A64-972D-B91175A8D6BB}'"
SQL: WHERE Key IN (@KeyList)
How ever this does not work.
Is there a better way to sent a list/array of GUIDS to SQL and use it to retrieve data. SQL2K5 doesnt have a ARRAY type.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

SQL Server has no arrays or lists. You can try sending it as XML and then use Xquery to get the values that were sent. Another alternative is tor send it as in comma separated list and write and use your own function that can take such a string and return a table that that contains the values. A different approach is to use dynamic SQL with the parameter that you are sending. From those 3 approaches, the XML is my favorite.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Garadin One Orange Chip Points: 29613 More actions · Answer 2

If you decide to go the Dynamic SQL Route (which is the easiest to implement from where you are now), make sure you keep injection in mind and code accordingly.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

Try this link

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

Clear Sky SQL
My Blog[/url]

Rofty SSCrazy Points: 2585 More actions · Answer 4

Thanks for your replies everyone.

Seems one can always track n problem like this back to Database Design. I adjusted the design of the tables to include specific Grouping ID's that should have been present in the first place.

Using the grouping ID's i can now refer to n-amount of records by simply sending the procedure an appropriate Grouping ID instead of a comma delimited list of GUID's, and then using complex solution algorithms to breakdown the values.

Of course designing applications on Databases with weak designs tend to present a lot of problem.

Thanks Again.

[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]