代码之家 › 专栏 › 技术社区 › Toby

生成简单的CRUD存储过程

code-generation stored-procedures sql-server

Toby · 技术社区 · 16 年前

我正在从事一个项目,该项目受某些与SQL实现相关的公司标准的约束。特别是,所有SQL Server内容都可以访问通过存储过程。(没有ORM或LINQ。)

80%或更多的需求可以通过基本的CRUD(CREATE,READ,UPDATE,DELETE)类型的过程来处理,这个过程应该很容易生成。然而,到目前为止,我还没有找到一个能够生成这些相当简单的“基本”存储过程的现有工具。

那么,有人能告诉我一个工具,我可以用它来生成我所需的大部分过程吗?最好是允许对流程进行一些定制的,比如将语句包装在basic中 BEGIN/END TRY

谢谢。

2 回复 | 直到 15 年前

Community Mohan Dere 9 年前

SSMS Tools Pack Mladen PrajdiÄ 听起来好像很合算。您可以自定义它使用的模板。

不管怎样,它的其他便利功能都值得一试。

Sam 16 年前

ssms tools pack声称可以做到这一点: http://www.ssmstoolspack.com/

USE [AdventureWorks];
GO

IF OBJECT_ID('[Person].[usp_AddressSelect]') IS NOT NULL
BEGIN 
    DROP PROC [Person].[usp_AddressSelect] 
END 
GO
CREATE PROC [Person].[usp_AddressSelect] 
    @AddressID INT
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    SELECT [AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate] 
    FROM   [Person].[Address] 
    WHERE  ([AddressID] = @AddressID OR @AddressID IS NULL) 

    COMMIT
GO
IF OBJECT_ID('[Person].[usp_AddressInsert]') IS NOT NULL
BEGIN 
    DROP PROC [Person].[usp_AddressInsert] 
END 
GO
CREATE PROC [Person].[usp_AddressInsert] 
    @AddressLine1 nvarchar(60),
    @AddressLine2 nvarchar(60),
    @City nvarchar(30),
    @StateProvinceID int,
    @PostalCode nvarchar(15),
    @rowguid uniqueidentifier,
    @ModifiedDate datetime
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    INSERT INTO [Person].[Address] ([AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate])
    SELECT @AddressLine1, @AddressLine2, @City, @StateProvinceID, @PostalCode, @rowguid, @ModifiedDate

    -- Begin Return Select <- do not remove
    SELECT [AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate]
    FROM   [Person].[Address]
    WHERE  [AddressID] = SCOPE_IDENTITY()
    -- End Return Select <- do not remove

    COMMIT
GO
IF OBJECT_ID('[Person].[usp_AddressUpdate]') IS NOT NULL
BEGIN 
    DROP PROC [Person].[usp_AddressUpdate] 
END 
GO
CREATE PROC [Person].[usp_AddressUpdate] 
    @AddressID int,
    @AddressLine1 nvarchar(60),
    @AddressLine2 nvarchar(60),
    @City nvarchar(30),
    @StateProvinceID int,
    @PostalCode nvarchar(15),
    @rowguid uniqueidentifier,
    @ModifiedDate datetime
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    UPDATE [Person].[Address]
    SET    [AddressLine1] = @AddressLine1, [AddressLine2] = @AddressLine2, [City] = @City, [StateProvinceID] = @StateProvinceID, [PostalCode] = @PostalCode, [rowguid] = @rowguid, [ModifiedDate] = @ModifiedDate
    WHERE  [AddressID] = @AddressID

    -- Begin Return Select <- do not remove
    SELECT [AddressID], [AddressLine1], [AddressLine2], [City], [StateProvinceID], [PostalCode], [rowguid], [ModifiedDate]
    FROM   [Person].[Address]
    WHERE  [AddressID] = @AddressID 
    -- End Return Select <- do not remove

    COMMIT TRAN
GO
IF OBJECT_ID('[Person].[usp_AddressDelete]') IS NOT NULL
BEGIN 
    DROP PROC [Person].[usp_AddressDelete] 
END 
GO
CREATE PROC [Person].[usp_AddressDelete] 
    @AddressID int
AS 
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    DELETE
    FROM   [Person].[Address]
    WHERE  [AddressID] = @AddressID

    COMMIT
GO

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------