T-SQL：如何在存储过程中创建“私有”功能

Okay so I'm writing a SQL Server 2008 Stored Procedure (maintenance script).

好吧，我正在编写一个SQL Server 2008存储过程（维护脚本）。

In doing so, being a good boy I've done plenty of error handling, checking rowcounts, printing output messages, etc

在这样做时，作为一个好孩子，我已经做了大量的错误处理，检查行计数，打印输出消息等

But in doing this, I've found myself writing over and over again something like this:

但是在这样做的过程中，我发现自己一遍又一遍地写着这样的东西：

SELECT @RowsAffected = @@ROWCOUNT
IF @RowsAffected > 0
BEGIN
   PRINT CAST(@RowsAffected, NVARCHAR(2)) + 'rows updated.'
END

Or debug messages like this:

或者像这样调试消息：

PRINT 'User ' + CAST(@UserId AS NVARCHAR(5)) + ' modified successfully'

Is there a way I can create a kind of 'subroutine' inside the stored procedure (like a private method) that can accept something as a parameter (doesn't have to though) and do some logic?

有没有办法可以在存储过程中创建一种“子例程”（如私有方法），它可以接受某些东西作为参数（但不是必须）并做一些逻辑？

I want to be able to do something like this:

我希望能够做到这样的事情：

CheckRowCounts

Or this:

或这个：

PrintUserUpatedMessage(@UserId)

Which would then perform the above logic (check rowcount, print message, etc)

然后执行上述逻辑（检查rowcount，打印消息等）

And yes obviously I can create a UDF, but then i would need to create/drop it etc as this logic is only required for the life of the execution of this stored procedure.

是的，显然我可以创建一个UDF，但后来我需要创建/删除它等，因为这个逻辑只需要执行这个存储过程的生命周期。

Getting sick and tired of writing the same code over and over again, and changing all the different areas I've used it when I get an error =)

生病和厌倦了一遍又一遍地编写相同的代码，并且当我收到错误时改变我用过它的所有不同区域=）

Can anyone help?

有人可以帮忙吗？

EDIT

编辑

Ok so I ended up creating a scalar UDF function (seems only way).

好的，所以我最终创建了一个标量UDF函数（似乎只有这样）。

However, I have awarded the correct answer to Fredrik as although I don't plan to implement this, it is both a correct answer and a creative one at that.

但是，我已经给Fredrik一个正确的答案，虽然我不打算实现这个，但它既是正确的答案，也是一个创造性的答案。

Thanks for all the advice/help.

感谢您的所有建议/帮助。

2 个解决方案

#1

I first tried to create another, temporary SP, from within an existing SP - which didn't work, but after experimenting a bit I think you could go with something like this (if you don't mind dynamic SQL):

我首先尝试在现有的SP中创建另一个临时SP - 这不起作用，但经过实验后我觉得你可以选择这样的东西（如果你不介意动态SQL）：

CREATE PROCEDURE sp_myTest_v1_0(@firstName NVARCHAR(255)) AS
BEGIN
    -- declare private method
    DECLARE @privateMethod NVARCHAR(255), @privateMethodSig NVARCHAR(255)
    SELECT @privateMethod = 
        'DECLARE @x INT' + CHAR(10) +
        'WHILE ISNULL(@x,0) < 10 BEGIN' + CHAR(10) +
            'PRINT @param1 + CAST(@x AS VARCHAR)' + CHAR(10) +
            'SET @x = ISNULL(@x,0)+1' + CHAR(10) +
        'END', @privateMethodSig = '@param1 NVARCHAR(255)'

    -- call privateMethod
    EXEC sp_executesql @privateMethod, @privateMethodSig, @param1 = @firstName
END
GO

#2

not really anything like that in tsql. the closest thing, as you stated, is a scalar udf, and you don't seem to be a fan of that idea. i don't see the issue with creating some helper functions like that and leaving them in the database. surely you have other procedures that could benefit from good messages.

在tsql中不是那样的。正如你所说，最接近的是标量udf，你似乎并不喜欢这个想法。我没有看到创建这样的辅助函数并将它们留在数据库中的问题。当然，你有其他程序可以从好消息中受益。