SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Passing a List/Array Parameter to Procedure


Passing a List/Array Parameter to Procedure

Author
Message
Rofty
Rofty
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 274
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.

-----------------------------------------------------------------------------------------------------------------------
:-D Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. :-D
-----------------------------------------------------------------------------------------------------------------------

Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3707 Visits: 6512
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
Garadin
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2797 Visits: 4107
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
Dave Ballantyne
Dave Ballantyne
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2952 Visits: 8370
Try this link


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



Clear Sky SQL
My Blog
Rofty
Rofty
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 274
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.

-----------------------------------------------------------------------------------------------------------------------
:-D Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. :-D
-----------------------------------------------------------------------------------------------------------------------

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search