execution of a stored procedure using multiple values for a single parameter

Question

0.00/5 (No votes)

See more:

Hello
I need urgently your help. I have a stored procedure with 1 single parameter (@code)

SQL

e.g create PROCEDURE george
@code int
begin 
...........

the send me an excel which has the codes ( for @code) that i must to execute
e.g excel has 1 column (code) and 3 rows (1111, 2222,3333)

the problem is when i am going to execute it i have to give

SQL

EXEC george @code = '1111'
EXEC george @code = '2222'
EXEC george @code = '3333'

in order to execute ( in series). but when the excel file have 100 rows this is a problem

could you tell a way ( to write different maybe the procedure or something else) so that i have to give only ONCE execution for the procedure and this executes all the codes from the excel (( in series) one after the other.
thanks in advance

Posted 13-Nov-15 9:00am

Member 12137876

Updated 13-Nov-15 20:08pm

Maciej Los

v2

Add a Solution

Comments

ZurdoDev 13-Nov-15 15:17pm

You can use a cursor in SQL and take a delimited list.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-11-13T20:19:00

You can pass comma separated list of parameters as single parameter. Then - inside a sp - you have to split values into several rows. How? Please, see: Using comma separated value parameter strings in SQL IN clauses[^]

But i'd suggest to re-think your programme and create connection between Excel file and sql server database. Then you'll be able to select data based on relationship between databases, even if an Excel file will be having over 1M records. Another way is to create linked server[^].
Using a SQL Server Linked Server to Query Excel Files[^]
How to import data from Excel to SQL Server [^]
Moving Data From Excel to SQL Server - 10 Steps to Follow[^]
Simple way to import data into SQL Server[^]