存储过程变量 [英] Stored Procedure Variables

问题描述

恐怕我的术语妨碍了我在互联网上找到所需的结果.无论如何，我有一个相当复杂的存储过程，我需要将几个变量传递给它.不是通过存储过程多次输入这些变量，有没有办法在过程开始时定义一个变量，并在整个过程中引用该变量?例如

I am afraid my terminology has hindered me from finding the results I am looking for on the internet. Anyhow, I have a fairly complex stored procedure that I need to pass several variables to. Instead of entering these variables several time through the stored procedure, is there a way to define a variable at the beginning of the procedure, and reference that variable throughout the procedure? For example

ID 编号 = 1075、1050、1025

IDNumber = 1075, 1050, 1025

不必在整个过程中多次使用 where 子句，

Instead of having to use the where clause several time throughout the procedure,

WHERE IDNumber = 1075 AND IDNumber = 1050 AND IDNumber = 1025

WHERE IDNumber = 1075 AND IDNumber = 1050 AND IDNumber = 1025

有没有办法在单个变量中定义这些整数，并通过存储过程传递变量?

is there a way for me to define those integers in a single variable, and pass the variable through the stored procedure?

对于伴随此问题的任何混淆，抱歉，因为 SQL 对我来说非常陌生.感谢您的时间和帮助.

Sorry for any confusion that accompanies this questions, as SQL is very new to me. Thank you for your time and help.

我使用的是 SQL Server 2008 R2.

I'm using SQL Server 2008 R2.

推荐答案

是的，您希望将表值参数传递给存储过程.阅读这篇文章:

Yes you want to pass in a table valued parameter to your stored proc. Give this article a read:

http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/

基本上你将定义类型:

CREATE TYPE myType AS TABLE
(                     
       [myId] int NULL
)      

然后创建一个接受该类型的存储过程:

Then create a stored proc that accepts that type:

CREATE PROCEDURE myProc
(
      @TableVariable myType READONLY
)
AS
BEGIN
...

这篇关于存储过程变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！