Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Passing a list of variables to a stored procedure

Posted on 2002-04-15
6
Medium Priority
?
249 Views
Last Modified: 2013-12-24
Hi all,

I am looking for the most efficient way to pass a list, serie or array of variables to a SQL Server stored procedure and process it at once.  THks fr your help.  

Sincerely,

Racimo
0
Comment
Question by:Racim BOUDJAKDJI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Accepted Solution

by:
anrazame earned 150 total points
ID: 6941531
You can call a storproc with a param as varchar
MyStorProcParam="1,3,45"
and parse it in the body of your SP.

You need to make an CF output to this param:
MyStorProcParam="#myListAsString#"
0
 
LVL 1

Expert Comment

by:anrazame
ID: 6941539
Alternatively can you save your array in the temporary table and call the SP, that uses this table. You have nothing to parse then.
0
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6941544
anrazame,

Thks for the feedback.  I don't really need an output on this CFSTOREDPROC.  I know I would have to pass it as a varchar but that does not tell what would be the best way for SQL Server to process;  I need some kind of stored procedure example that does the job.

Sincerely,

Racimo
0
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6941555
I'd rather not do any unuseful write operation.  I thought about creating a temp table but I prefer to make a single process that processes the varchar. Is there some kind of SQL Server user-defined function that does the job?

Sincerely,

Racimo
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 6942220
I agree w/ anrazame, the simplest way is to pass the data comma delimited and make the stored proc handle it.

CJ
0
 
LVL 23

Author Comment

by:Racim BOUDJAKDJI
ID: 6944383
Hi guys,

I KNOW that passing it as a string would be the solution.  I was just expecting an optimized and complete solution.  BTW, I found a solution which is to create a T-SQL function to emulate the split VB function then use the CFSTOREDPROC to pass the varchar delimmited string.  I guess I should have asked this question on the SQL board...

As it's my mistake, I will grant the points to anrazame.

Here is the link to the solution I have found for anybody interested

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp
0

Featured Post

Understanding Web Applications

Without even knowing it, most of us are using web applications on a daily basis. Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We often confuse these web applications tools for websites.  So, what is the difference?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question