如何在 SQL Server 2005 Management Studio 中创建 SQL Server 2005 存储过程模板? [英] How do you create SQL Server 2005 stored procedure templates in SQL Server 2005 Management Studio?

问题描述

如何在 SQL Server 2005 Management Studio 中创建 SQL Server 2005 存储过程模板?

How do you create SQL Server 2005 stored procedure templates in SQL Server 2005 Management Studio?

推荐答案

另一个小块头，我认为它可以帮助人们开发数据库并提高他们的数据库开发效率.在开发软件解决方案时，我非常喜欢存储过程和函数.我喜欢在数据库级别实现我的实际 CRUD 方法.它允许我在应用程序软件(业务逻辑和数据访问)和数据库本身之间平衡我的工作.不想发起宗教战争，但我想让人们通过模板以最佳实践更快地开发存储过程.

Another little nugget that I think will help people developing and being more productive in their database development. I am a fan of stored procedures and functions when I develop software solutions. I like my actual CRUD methods to be implemented at the database level. It allows me to balance out my work between the application software (business logic and data access) and the database itself. Not wanting to start a religious war, but I want to allow people to develop stored procedures more quickly and with best practices through templates.

让我们从在 SQL Server 2005 management Studio 中制作您自己的模板开始.首先，您需要在 Studio 中显示模板资源管理器.

Let’s start with making your own templates in the SQL Server 2005 management Studio. First, you need to show the Template Explorer in the Studio.

替代文字 http://www.cloudsocket.com/images/image-thumb10.png

这将显示以下内容:

替代文字 http://www.cloudsocket.com/images/image-thumb11.png

替代文字 http://www.cloudsocket.com/images/image-thumb12.png

替代文字 http://www.cloudsocket.com/images/image-thumb13.png

IDE 将创建一个空白模板.要编辑模板，请右键单击模板并选择编辑.您将在 IDE 中看到一个空白的查询窗口.您现在可以插入您的模板实现.我在这里有包含 TRY CATCH 的新存储过程的模板.我喜欢在我的存储过程中包含错误处理.随着 SQL Server 2005 中 TSQL 新增的 TRY CATCH，我们应该尝试通过我们的代码(包括数据库代码)使用这种强大的异常处理机制.保存模板，您就可以使用新模板创建存储过程了.

The IDE will create a blank template. To edit the template, right click on the template and select Edit. You will get a blank Query window in the IDE. You can now insert your template implementation. I have here the template of the new stored procedure to include a TRY CATCH. I like to include error handling in my stored procedures. With the new TRY CATCH addition to TSQL in SQL Server 2005, we should try to use this powerful exception handling mechanism through our code including database code. Save the template and you are all ready to use your new template for stored procedure creation.

-- ======================================================
-- Create basic stored procedure template with TRY CATCH
-- ======================================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
    BEGIN TRY
        BEGIN TRANSACTION    -- Start the transaction

        SELECT @p1, @p2

        -- If we reach here, success!
        COMMIT
    END TRY
    BEGIN CATCH
        -- there was an error
        IF @@TRANCOUNT > 0
        ROLLBACK

        -- Raise an error with the details of the exception
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
        SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

        RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
GO

这篇关于如何在 SQL Server 2005 Management Studio 中创建 SQL Server 2005 存储过程模板?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！