使用多个值为单个参数执行存储过程 [英] execution of a stored procedure using multiple values for a single parameter

问题描述

您好

我急需您的帮助。我有一个带有1个参数的存储过程（ @code ）

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

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

给我发一个excel，其中包含我必须执行的代码（对于@code）

例如excel有1列（代码） ）和3行（ 1111,2222,3333 ）



问题是当我要执行它时我必须给

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

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

为了执行（串联）。但是当excel文件有100行时这是一个问题



你能说出一种方法（写不同的程序或别的东西）所以我必须给只有ONCE执行程序，这将执行excel中的所有代码（（串联）一个接一个。

提前感谢

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

推荐答案

您可以将逗号分隔的参数列表作为单个参数传递。然后 - 在sp内 - 您必须将值拆分为多行。如何？请参阅：在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]



但我建议重新考虑你的程序并在Excel文件和sql server数据库之间建立连接。然后你就可以了即使Excel文件的记录超过1M，也可以根据数据库之间的关系选择数据。另一种方法是创建链接服务器 [ ^ ]。

使用SQL Server链接服务器查询Excel文件 [ ^ ]

如何将数据从Excel导入SQL Server [ ^ ]

将数据从Excel传送到SQL Server - 要遵循的10个步骤 [ ^ ]

将数据导入SQL Server的简单方法 [ ^ ]

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[^]

这篇关于使用多个值为单个参数执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！