• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Passing a list of variables to a stored procedure

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
Racim BOUDJAKDJI
Asked:
Racim BOUDJAKDJI
  • 3
  • 2
1 Solution
 
anrazameCommented:
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
 
anrazameCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
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
 
cheekycjCommented:
I agree w/ anrazame, the simplest way is to pass the data comma delimited and make the stored proc handle it.

CJ
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now