Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Passing a List/Array Parameter to Procedure Expand / Collapse
Author
Message
Posted Thursday, January 7, 2010 3:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 10:24 PM
Points: 192, Visits: 229
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.






Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it.
Post #843405
Posted Thursday, January 7, 2010 5:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:21 AM
Points: 2,128, Visits: 5,561
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/
Post #843446
Posted Sunday, January 10, 2010 12:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 2:24 PM
Points: 1,519, Visits: 4,082
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #845092
Posted Sunday, January 10, 2010 1:02 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Try this link


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




Clear Sky SQL
My Blog
Kent user group
Post #845108
Posted Sunday, January 10, 2010 11:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 10:24 PM
Points: 192, Visits: 229
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.



Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it.
Post #845193
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse