Làm thế nào để phát hiện nếu một thủ tục được lưu trữ đã tồn tại?

Nếu bạn DROP và TẠO quy trình, bạn sẽ mất các cài đặt bảo mật. Điều này có thể gây khó chịu cho DBA của bạn hoặc phá vỡ ứng dụng của bạn hoàn toàn.

Những gì tôi làm là tạo ra một thủ tục lưu trữ tầm thường nếu nó chưa tồn tại. Sau đó, bạn có thể THAY ĐỔI thủ tục được lưu trữ theo ý thích của bạn.

IF object_id('YourSp') IS NULL
    EXEC ('create procedure dbo.YourSp as select 1')
GO
ALTER PROCEDURE dbo.YourSp
AS
...

Bằng cách này, cài đặt bảo mật, nhận xét và meta Deta khác sẽ tồn tại khi triển khai.

Cách sạch nhất là kiểm tra sự tồn tại của nó, thả nó nếu nó tồn tại và sau đó tạo lại nó. Bạn không thể nhúng câu lệnh "tạo Proc" bên trong câu lệnh IF. Điều này nên làm độc đáo:

IF OBJECT_ID('MySproc', 'P') IS NOT NULL
DROP PROC MySproc
GO

CREATE PROC MySproc
AS
BEGIN
    ...
END

Nếu bạn chỉ xử lý các thủ tục được lưu trữ, điều dễ nhất để làm là có thể bỏ Proc, sau đó tạo lại nó. Bạn có thể tạo tất cả các mã để thực hiện việc này bằng trình hướng dẫn Tạo tập lệnh trong SQL Server.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourSproc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[YourSproc]

CREATE PROCEDURE YourSproc...

Từ SQL Server 2016 CTP3bạn có thể sử dụng các câu lệnh DIE mới thay vì các IFhàm bao lớn

Cú pháp:

DROP {PROC | THỦ TỤC} [NẾU EXISTS] {[lược đồ tên. ] thủ tục} [, ... n]

Truy vấn:

DROP PROCEDURE IF EXISTS usp_name

Thêm thông tin ở đây

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
CREATE PROCEDURE dbo.xxx

nơi xxxlà tên proc

Ngoài những gì đã được nói, tôi cũng muốn thêm một cách tiếp cận khác và ủng hộ việc sử dụng chiến lược triển khai tập lệnh khác biệt. Thay vì tạo một tập lệnh trạng thái luôn kiểm tra trạng thái hiện tại và hành động dựa trên trạng thái đó, hãy triển khai thông qua một loạt các tập lệnh không trạng thái nâng cấp từ các phiên bản nổi tiếng . Tôi đã sử dụng chiến lược này và nó mang lại kết quả lớn vì các kịch bản triển khai của tôi giờ đây hoàn toàn miễn phí 'NẾU'.

IF OBJECT_ID('SPNAME') IS NULL
     -- Does Not Exists
ELSE
     -- Exists

Bạn có thể viết một truy vấn như sau:

IF OBJECT_ID('ProcedureName','P') IS NOT NULL
    DROP PROC ProcedureName
GO

CREATE PROCEDURE [dbo].[ProcedureName]
...your query here....

Để cụ thể hơn về cú pháp trên:
OBJECT_ID là số id duy nhất cho một đối tượng trong cơ sở dữ liệu, điều này được SQL Server sử dụng nội bộ. Vì chúng tôi đang truyền cho RoutureName, theo sau là loại đối tượng P , thông báo cho SQL Server rằng bạn sẽ tìm thấy đối tượng có tên là RoutureName , thuộc loại thủ tục, tức là

Truy vấn này sẽ tìm thủ tục và nếu có sẵn, nó sẽ bỏ nó và tạo một thủ tục mới.

Để biết thông tin chi tiết về các loại OBRI_ID và Object, vui lòng truy cập: SYS.Objects

Tùy chọn tốt hơn có thể là sử dụng một công cụ như So sánh SQL cổng đỏ hoặc Trình kiểm tra SQL để tự động so sánh sự khác biệt và tạo tập lệnh di chuyển.

Mã dưới đây sẽ kiểm tra xem thủ tục được lưu trữ đã tồn tại hay chưa.

Nếu nó tồn tại, nó sẽ thay đổi, nếu nó không tồn tại, nó sẽ tạo ra một thủ tục lưu trữ mới cho bạn:

//syntax for Create and Alter Proc 
DECLARE @Create NVARCHAR(200) = 'Create PROCEDURE sp_cp_test'; 
DECLARE @Alter NVARCHAR(200) ='Alter PROCEDURE sp_cp_test'; 
//Actual Procedure 
DECLARE @Proc NVARCHAR(200)= ' AS BEGIN select ''sh'' END'; 
//Checking For Sp
IF EXISTS (SELECT * 
           FROM   sysobjects 
           WHERE  id = Object_id('[dbo].[sp_cp_test]') 
                  AND Objectproperty(id, 'IsProcedure') = 1 
                  AND xtype = 'p' 
                  AND NAME = 'sp_cp_test') 
  BEGIN 
      SET @Proc=@Alter + @Proc 

      EXEC (@proc) 
  END 
ELSE 
  BEGIN 
      SET @Proc=@Create + @Proc 

      EXEC (@proc) 
  END 

go 

Tôi có một Proc được lưu trữ cho phép khách hàng mở rộng xác nhận, nếu nó tồn tại tôi không muốn thay đổi nó, nếu tôi không muốn tạo nó, cách tốt nhất tôi đã tìm thấy:

IF OBJECT_ID('ValidateRequestPost') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE ValidateRequestPost 
    @RequestNo VARCHAR(30),
    @ErrorStates VARCHAR(255) OUTPUT
AS
BEGIN
    SELECT @ErrorStates = @ErrorStates
END')
END