SQL Server中的部分存储过程-CSDN博客

介绍 (Introduction)

SQL is an old language — almost 50 years! The first commercially-available version of SQL was released by Oracle way back in 1969. In its specifications and general, “standard” appearance, it resembles the other leading language of the day, COBOL. Language theory and computer languages have evolved considerably since then. Modern concepts such as object-oriented programming and functional programming are not well-represented in SQL. That’s a pity. It can lead to copying code which almost always increases maintenance costs and code fragility.

SQL是一门古老的语言-近50年了！ Oracle于1969年发布了第一个商业可用SQL版本。在其规范和一般的“标准”外观方面，它类似于当今的另一种主要语言COBOL。从那时起，语言理论和计算机语言有了长足的发展。诸如面向对象的编程和函数式编程之类的现代概念在SQL中并未得到很好的体现。真可惜。它可能导致复制代码，这几乎总是增加维护成本和代码脆弱性。

One thing that I try to adhere to — in every language I use — is the Don’t Repeat Yourself, or DRY, principle. In other articles and blogs, I’ve discussed using CROSS APPLY as a way to eliminate repeated expressions. In this one, I want to explore another way to make your code DRYer.

我尝试使用的每一种语言都坚持的一件事是“不要重蹈覆辙”或“干燥”的原则。在其他文章和博客中，我讨论了使用CROSS APPLY作为消除重复表达式的方法。在本教程中，我想探讨另一种使代码DRYer的方法。

争论太多！ (Too many arguments!)

One principle from the functional programming world is that a function should have at most 3 or 4 arguments. Any more and the function is probably trying to do too much. In the SQL world, this would apply to both functions and stored procedures. Nevertheless, I see this principle violated with alarming regularity. What can we do about it? Let’s borrow another technique from the functional programming world: partial functions.

函数式编程世界的一个原则是，一个函数最多应具有3或4个参数。再多了，该功能可能试图做太多。在SQL世界中，这将同时适用于函数和存储过程。然而，我看到这一原则被惊人地违反了。我们对于它可以做些什么呢？让我们借鉴函数式编程世界的另一种技术：部分函数。

The idea sounds simple. Let’s look at an example in Python. (Don’t worry if you don’t know Python. It’s rightly called “the language that’s easy to learn and hard to forget!”). I’ll define a simple function:

这个想法听起来很简单。让我们看一下Python中的示例。（不用担心，如果您不懂Python。它被正确地称为“易于学习且难以忘记的语言！”）。我将定义一个简单的函数：

def myfunc(a, b, c):
   print(a, b, c)

OK, that’s not too exciting. However, imagine that, in some section of code, I call myfunc 10 times and for all ten calls, only argument c changes. I’d have to repeat arguments a and b 10 times. What if I could somehow encapsulate them in a custom function so that I only needed to specify argument c? I can do it like this:

好吧，那不太令人兴奋。但是，想象一下，在代码的某些部分中，我多次调用myfunc 10次，而对于所有十次调用，仅参数c发生了变化。我必须将参数a和b重复10次。如果可以将它们封装在自定义函数中，以便只需要指定参数c怎么办？我可以这样：

def partial_a_b(a, b):
def f(c):
myfunc(a, b, c)
return f

def partial_a_b（a，b）：
定义f（c）：
myfunc（a，b，c）
返回f

def partial_a_b(a, b):
    def f(c):
        myfunc(a, b, c)
    return f
 
# Create new function, using variables a and b
myfunc_c = partial_a_b("The Answer", "is")
 
# Now, only specify argument c
myfunc_c(42)

Here, I create a function called partial_a_b that creates a new function each time it is called. The new function encapsulates the values of arguments a and b, just leaving argument c exposed, then returns the function. Then, I use partial_a_b to create another function I call myfunc_c. This snippet yields:

在这里，我创建了一个名为partial_a_b的函数，该函数在每次调用时都会创建一个新函数。新函数封装了参数a和b的值，只暴露了参数c，然后返回该函数。然后，我使用partial_a_b创建另一个函数，称为myfunc_c。该代码段产生：

The Answer is 42

答案是42

In Python, functions are first-class. They can be passed around like other objects and returned from other functions, as above. The function partial_a_b encapsulates arguments a and b. We can also say that the function closes over those arguments and that the partial_a_b is a closure. Closures are a typical way to create partial functions, since they close over some of the arguments while leaving the others exposed.

在Python中，函数是一流的。如上所述，它们可以像其他对象一样传递，并从其他函数返回。函数partial_a_b封装参数a和b。我们也可以说函数关闭了这些参数，而partial_a_b是闭包。闭包是创建部分函数的一种典型方法，因为闭包会覆盖某些参数而其他参数则暴露在外。

Writing partial functions is easy in Python, but they are not easy to write in all languages. For example, in an article by Jon Skeet, a .NET guru, he shows how to write them in C#, which is not a functional language. While possible, it is a little complicated. See the references section for a link to that article if you want to know more.

在Python中编写部分函数很容易，但是在所有语言中编写它们并不容易。例如，在.NET专家Jon Skeet的一篇文章中，他展示了如何用C＃（不是一种功能语言）编写它们。虽然可能，但有些复杂。如果您想了解更多信息，请参见参考资料部分，以获得该文章的链接。

ANSI SQL is another language where writing partial functions are challenging and are dependent on the dialect. Since I’m talking about SQL Server, that means the T-SQL dialect. Functions are not first-class objects in ANSI SQL or T-SQL but we can achieve the same goal using dynamic SQL. Let’s look at an example from a real project I’m working with.

ANSI SQL是另一种语言，其中编写部分函数很有挑战性，并且依赖于方言。因为我在谈论SQL Server，所以这就是T-SQL方言。函数不是ANSI SQL或T-SQL中的一流对象，但是我们可以使用动态SQL达到相同的目标。让我们看一个我正在处理的真实项目的示例。

记录示例 (A logging example)

Before learning about Partial stored procedures in SQL Server, in case you want to walk through the basics of Stored procedures and understand how they are different from functions, I would recommend you to read these informative articles:

在学习SQL Server中的部分存储过程之前，如果您想遍历存储过程的基础知识并了解它们与功能的不同之处，建议您阅读以下内容丰富的文章：

One system that I help support uses a logging system that is invoked with a stored procedure call. (Note that I’m not arguing the merits of doing logging this way. That could be a subject for another article.) The logger proc has 10 parameters, breaking the rule about 3 or 4 that is typical in functional languages. Furthermore, for a given calling program on a typical run, which may be another stored procedure, 5 or more of those 10 parameters will not change whether the logger is called once or a hundred times. It would be great to be able to build a “partial” procedure that closes over those five, fixed arguments and exposes the rest.

我帮助支持的一个系统使用一个日志记录系统，该系统通过存储过程调用来调用。（请注意，我并不是在争论以这种方式进行日志记录的优点。这可能是另一篇文章的主题。）记录器proc具有10个参数，打破了函数语言中常见的3或4个规则。此外，对于典型运行中的给定调用程序（可能是另一个存储过程），无论记录器被调用一次还是百次，这10个参数中的5个或更多都不会更改。能够构建一个“部分”过程来封闭这五个固定的参数并公开其余部分，这将是很棒的。

Let’s see how to do that with something simpler than my ten-parameter logger:

让我们看看如何用比我的十参数记录器更简单的方法来做到这一点：

CREATE PROC logger 
       @jobid int, 
       @jobname varchar(50), 
       @jobdate date, 
       @logmsgid int, 
       @logmsg varchar(255)
AS BEGIN
    PRINT CONCAT(@jobid, '/', @jobname, '/', @jobdate, '/', @logmsgid, '/', @logmsg);
END
 
EXEC logger 1, 'The Ultimate Question','20120601', 42, 'The Answer'

The EXEC call produces:

EXEC调用产生：

No surprise there.

毫不奇怪。

This little logger doesn’t do much, I’ll admit, but it’s enough to see how to build a partial procedure in T-SQL. Suppose this is called from another stored procedure that needs to log its progress for later analysis. Assume that, for the calling procedure, the first three arguments do not change for a given run. Further, assume that the calling proc has ten places where it wants to call the logger. A typical call might look like:

我承认，这个小小的记录器并没有做什么用，但是足以了解如何在T-SQL中构建部分过程。假设这是从另一个需要记录其进度以供以后分析的存储过程中调用的。假定对于调用过程，对于给定的运行，前三个参数不变。此外，假设调用proc有十个地方要调用记录器。一个典型的呼叫可能看起来像：

EXEC logger 
       @jobid=@jobid, 
       @jobname = @jobname, 
       @jobdate = @jobdate, 
       @logmsgid = 42, 
       @logmsg = 'The Answer';

The first three parameters are variables set elsewhere in the calling proc. Only the last two vary from call to call. What I want is to be able to call something like:

前三个参数是在调用过程中其他位置设置的变量。每个呼叫之间只有后两个有所不同。我想要的是能够打电话这样的东西：

EXEC mylogger @logmsgid = 42, @logmsg = 'The answer';

If I can do that, I’ll avoid repeating myself. That is, the code will be DRYer. What I need to do is build a helper proc, which I’ll call GetLogger, that will take in all the arguments that the logger proc has and creates a new, temporary stored procedure called #mylogger. Using a temporary stored procedure makes sense since it is only exposed to the current session, which is the session having the repeated arguments. Here’s the signature of GetLogger:

如果可以，我将避免重复自己。也就是说，代码将为DRYer。我需要做的是构建一个帮助程序proc，我将其称为GetLogger，它将吸收记录程序proc拥有的所有参数，并创建一个名为#mylogger的新的临时存储过程。使用临时存储过程是有意义的，因为它仅公开给当前会话，即具有重复参数的会话。这是GetLogger的签名：

CREATE PROC GetLogger 
    @logger sysname = '#logger', 
    @jobid int = NULL, 
    @jobname varchar(50) = NULL, 
    @jobdate date = NULL, 
    @logmsgid int= NULL, 
    @logmsg varchar(255) = NULL

The sharp-eyed will notice that I’ve added one additional parameter and set the rest to have default values of NULL. I’ll use the @logger parameter to name the partial proc I’ll create and check the NULL values to determine which parameters to close over and which to expose. In the calling proc, I’ll call GetLogger like this:

敏锐的眼睛会注意到我添加了一个附加参数，并将其余参数设置为具有默认值NULL。我将使用@logger参数命名我将创建的部分proc，并检查NULL值以确定哪些参数要关闭以及哪些参数要公开。在调用过程中，我将这样调用GetLogger：

EXEC GetLogger 
    @logger=N'#mylogger', 
    @jobid=@jobid, 
    @jobname=@jobname, 
    @jobdate=@jobdate

GetLogger will then create a session-level, temporary procedure called #mylogger, closing over the three non-null arguments and exposing the other two. Let’s see how that works:

然后，GetLogger将创建一个名为#mylogger的会话级临时过程，关闭三个非空参数，并公开其他两个。让我们看看它是如何工作的：

DECLARE @closed  NVARCHAR(MAX) = N'';
DECLARE @exposed NVARCHAR(MAX) = N'';
DECLARE @params  NVARCHAR(MAX) = N'';
 
-- Check @jobid parameter
IF @jobid is not null 
   SET @closed += CONCAT(N'@jobid = ', @jobid, N', ')
ELSE BEGIN
   SET @exposed += N'@jobid int = NULL, ';
   SET @passed  += N'@jobid = @jobid, ';
END

All this does is see if the @jobid parameter is specified or not (that is, not NULL). If so, it is appended to a variable called @closed; if not, it is appended to the @exposed variable. A third variable, @passed, is also built up to use in the call to the original logger procedure defined above and pass the exposed parameters to it.

所有这一切都是看是否指定了@jobid参数（即，不是NULL）。如果是这样，则将其附加到名为@closed的变量；如果不是，它将附加到@exposed变量。还建立了第三个变量@passed，以用于对上面定义的原始记录器过程的调用中，并将公开的参数传递给它。

GetLogger does the same processing for all 5 parameters, then builds a CREATE PROC statement:

GetLogger对所有5个参数进行相同的处理，然后构建一个CREATE PROC语句：

-- Assemble dynamic SQL to create the partial stored procedure
DECLARE @stmt NVARCHAR(MAX) = CONCAT(
    N'CREATE PROC ', @logger, N' ', 
    LEFT(@exposed, LEN(@exposed)-1), N' ',
    N'AS EXEC logger ', 
    @closed, @passed
);
 
-- Change trailing comma to a semicolon
SET @stmt = LEFT(@stmt, len(@stmt)-1) + N';'
 
-- Create temporary partial stored procedure, dropping any existing one
DECLARE @dropExisting NVARCHAR(MAX) = CONCAT(N'DROP PROCEDURE IF EXISTS ', @logger);
EXEC  sp_executesql @stmt = @dropExisting;
PRINT @stmt;
EXEC  sp_executesql @stmt;

Now, let’s test this! I call the GetLogger procedure as shown above, which produces one line of output (shown wrapped here for easier reading):

现在，让我们测试一下！我如上所述调用GetLogger过程，该过程产生一行输出（此处显示为包装，以便于阅读）：

Now let’s call the partial function:

现在让我们调用偏函数：

EXEC #mylogger 1, 'The Ultimate Question'

This yields:

这样产生：

This is the same in the original call. The temporary, partial stored procedure works!

这与原始呼叫中的相同。临时的部分存储过程有效！

摘要 (Summary)

This article shows an approach for building partially stored procedures in T-SQL. This basic approach can be enhanced to suit your needs. For example, in my actual implementation, I add an @debug parameter, which causes the GetLogger proc to create a print statement instead of calling the original procedure. I’ll leave that detail as an exercise for the reader!

本文介绍了一种在T-SQL中构建部分存储过程的方法。可以增强此基本方法以适合您的需求。例如，在我的实际实现中，我添加了一个@debug参数，该参数导致GetLogger proc创建打印语句，而不是调用原始过程。我会将这些细节留给读者练习！

You may be wondering if the same technique can be applied to T-SQL functions. In short, it can, with care. Since there is no such thing as a temporary function in T-SQL, you would need to find a way to avoid collisions, in case two sessions tried to create two partial functions at the same time with different parameter values. You see, you have to use a regular function name. You could generate a name for the partial function at runtime, perhaps using the NEWID() function. But then, any place you call your partial function would need to be dynamic SQL, an extra complication (and a second exercise for the reader!)

您可能想知道是否可以将相同的技术应用于T-SQL函数。简而言之，它可以小心翼翼。由于T-SQL中没有临时函数，因此，如果两个会话试图同时使用不同的参数值创建两个部分函数，则需要找到一种避免冲突的方法。您会看到，必须使用常规函数名称。您可以在运行时生成局部函数的名称，也许使用NEWID（）函数。但是随后，您调用部分函数的任何地方都将需要动态SQL，这会增加额外的复杂性（这是读者的第二个练习！）

Others may wonder if I could write a generic stored procedure that would create a partially stored procedure from any other procedure. That would entail some way to call a stored procedure with arbitrary arguments. In pure T-SQL, this is not possible. The system stored procedure I used, sp_executesql, does do this, however. That’s because it is written in CLR code (e.g. C++, C#, etc.) and such functions can indeed take arbitrary parameters.

其他人可能想知道我是否可以编写一个通用存储过程，该通用存储过程将根据任何其他过程创建部分存储过程。这将需要某种方式来调用带有任意参数的存储过程。在纯T-SQL中，这是不可能的。但是，我使用的系统存储过程sp_executesql 可以做到这一点。那是因为它是用CLR代码（例如C ++，C＃等）编写的，并且此类函数确实可以采用任意参数。