저장 프로시저를 만들기 전에 저장 프로시저가 있는지 확인하는 방법

저장 프로시저를 만들기 전에 저장 프로시저가 있는지 확인하는 방법

클라이언트가 "데이터베이스 관리" 기능을 실행할 때마다 실행해야 하는 SQL 스크립트가 있습니다.이 스크립트에는 클라이언트 데이터베이스에 저장 프로시저를 작성하는 작업이 포함됩니다.이러한 클라이언트 중에는 스크립트를 실행할 때 이미 저장 프로시저가 있는 클라이언트도 있고 없는 클라이언트도 있습니다.누락된 스토어드 프로시저를 클라이언트 데이터베이스에 추가해야 하는데 T-SQL 구문을 아무리 구부려도 상관없습니다.

CREATE/ALTER PROCEDURE'는 쿼리 배치의 첫 번째 문이어야 합니다.

작품을 만들기 전에 드롭을 읽은 적이 있지만, 그런 식으로 하는 것은 좋아하지 않습니다.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO

CREATE PROCEDURE MyProc
...

저장 프로시저의 존재 여부 확인을 추가하고 없으면 생성하고, 없으면 변경하려면 어떻게 해야 합니까?

이미 답변으로 표시되어 있는 것은 알고 있습니다만, 이전에는 다음과 같이 하고 있었습니다.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))
   exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
  ....

그냥 절차 중지를 피하려고요

쿼리를 실행할 수 있는 모든 위치에서 절차 코드를 실행할 수 있습니다.

에 다 돼요.AS:

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

이 코드는 저장된 proc와 동일한 작업을 수행하지만 데이터베이스 측에는 저장되지 않습니다.

은 '에서 유사합니다.PL/SQL.

업데이트:

질문 제목이 좀 헷갈리네요.

프로시저가 존재하지 않는 경우에만 프로시저를 작성하면 됩니다.

SSMS을 사용하다

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …

업데이트:

스키마를 포함할 때 수행하는 방법의 예:

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[MyProc]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[MyProc]
END

위의 예에서는 dbo가 스키마입니다.

업데이트:

SQL Server 2016+에서는 다음과 같은 작업을 수행할 수 있습니다.

CREATE OR ALTER PROCEDURE dbo.MyProc

데이터베이스 오브젝트를 삭제하기 전에 그 존재를 확인하는 가장 간단한 방법을 찾고 있는 경우, 한 가지 방법이 있습니다(위의 예와 같이 SPROC를 사용하지만 테이블, 인덱스 등에 대해 수정할 수 있습니다).):

IF (OBJECT_ID('MyProcedure') IS NOT NULL)
  DROP PROCEDURE MyProcedure
GO

이 방법은 빠르고 우아하지만 모든 개체 유형에서 고유한 개체 이름을 사용해야 합니다. 이러한 이름은 고려되지 않습니다.

'있을 경우 프로시저를 변경하고 없을 경우 프로시저를 작성한다'는 것은 알지만, 저는 다음과 같이 하는 것이 더 간단하다고 생각합니다.

1. 프로시저가 이미 있는 경우는, 드롭 해 주세요.
2. 다시 만듭니다.

다음과 같이 합니다.

IF OBJECT_ID('MyProcedure', 'P') IS NOT NULL
    DROP PROCEDURE MyProcedure
GO

CREATE PROCEDURE MyProcedure AS
BEGIN
    /* ..... */
END
GO

두 번째 파라미터는 다음과 같은 오브젝트만 찾도록 지시합니다.object_type = 'P'스토어드 프로시저:

AF = 집계 함수(CLR)

C = CHECK 제약 조건

D = DEFAULT(제한 또는 독립 실행형)

F = 외부 키 제약 조건

FN = SQL 스칼라 함수

FS = 조립체(CLR) 스칼라 기능

FT = 조립체(CLR) 테이블 값 함수

IF = SQL 인라인 테이블 값 함수

IT = 내부 테이블

P = SQL 저장 프로시저

PC = 조립체(CLR) 저장 위치

PG = 계획 가이드

PK = 기본 키 제약 조건

R = 규칙(구식, 독립형)

RF = Replication-filter-Procedure

S = 시스템 기반 표

SN = 동의어

SO = Sequence 객체

TF = SQL 테이블 값 함수

TR = 트리거

모든 옵션 목록은 다음 웹 사이트를 통해 얻을 수 있습니다.

SELECT name 
FROM master..spt_values
WHERE type = 'O9T'

。DROP PROCEDURE IF EXISTS.
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]

참고 자료 : https://msdn.microsoft.com/en-us/library/ms174969.aspx

매우 오래된 게시물인 것은 알지만, 이 게시물이 상위 검색 결과에 표시되므로 SQL Server 2016 SP1을 사용하는 사용자를 위한 최신 업데이트를 추가합니다.

create or alter procedure procTest
as
begin
 print (1)
end;
go

저장 프로시저가 아직 없는 경우 생성되지만 있는 경우 변경됩니다.

언급

DROP IF EXISTES는 SQL Server 2016의 새로운 기능입니다.

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

DROP  PROCEDURE IF EXISTS dbo.[procname]

저도 같은 실수를 했어요.이 스레드는 이미 거의 비활성 상태입니다만, 「익명의 프로시저」외에 다른 옵션을 설정합니다.

이렇게 해결했습니다.

1. 저장 프로시저가 있는지 확인합니다.

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='my_procedure') BEGIN
       print 'exists'  -- or watever you want
   END ELSE BEGIN
       print 'doesn''texists'   -- or watever you want
   END
   

2. , 「」, 「」는"CREATE/ALTER PROCEDURE' must be the first statement in a query batch"요.이렇게 해결했습니다.

   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE -- view procedure function or anything you want ...
   

3. 이 코드로 끝납니다.

   IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('my_procedure'))
   BEGIN
       DROP PROCEDURE my_procedure
   END
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE PROCEDURE [dbo].my_procedure ...
   

여기 이렇게 사용하는 방법과 몇 가지 이유가 있습니다.저장된 프로시저를 편집하는 것은 그다지 예쁘지 않지만 장단점이 있습니다.

업데이트: 이 콜 전체를 TRANSACTION으로 래핑할 수도 있습니다.단일 트랜잭션에 많은 저장 프로시저를 포함하면 모두 커밋하거나 롤백할 수 있습니다.트랜잭션 래핑의 또 다른 장점은 다른 SQL 연결이 READ UNCOMMITED 트랜잭션 분리 수준을 사용하지 않는 한 저장 프로시저가 항상 존재한다는 것입니다.

1) 프로세스의 결정으로서 변경을 회피한다.델의 프로세스는 존재하는 경우 항상 드롭 후 작성하는 것입니다.새로운 PROC가 바람직한 Proc라고 가정하는 동일한 패턴을 실행하면 IF EXISTES ALTER ELSE CREATE가 생성되기 때문에 변경에 대응하기가 조금 어렵습니다.

2) CREATE/ALTER를 일괄적으로 첫 번째 호출로 배치해야 하므로 일련의 프로시저 업데이트를 동적 SQL 외부 트랜잭션으로 래핑할 수 없습니다.기본적으로 DB 백업을 복원하지 않고 전체 절차 업데이트 스택을 실행하거나 모두 롤백하려면 이 방법이 모든 작업을 단일 배치로 수행할 수 있습니다.

IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc 
    from sys.procedures sp
    join sys.schemas ss on sp.schema_id = ss.schema_id
    where ss.name = 'dbo' and sp.name = 'MyStoredProc')
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    -- Not so aesthetically pleasing part. The actual proc definition is stored
    -- in our variable and then executed.
    SELECT @sql = 'CREATE PROCEDURE [dbo].[MyStoredProc]
(
@MyParam int
)
AS
SELECT @MyParam'
    EXEC sp_executesql @sql
END

SQL Server 2008 이후에서는 "를 사용할 수 있습니다.INFORMATION_SCHEMA.ROUTINES"

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'MySP'
        AND ROUTINE_TYPE = 'PROCEDURE') 

**T-Sql에서 저장된 proc를 드롭하고 다시 작성하는 가장 간단한 방법은 **입니다.

Use DatabaseName
go
If Object_Id('schema.storedprocname') is not null
begin
   drop procedure schema.storedprocname
end
go

create procedure schema.storedprocname
as

begin
end

여기 제가 사용하는 대본이 있습니다.이것에 의해, 불필요하게 보존된 프로를 드롭 해 재작성하는 것을 피할 수 있습니다.

IF NOT EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[uspMyProcedure]')
    )
BEGIN
  EXEC sp_executesql N'CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1'
END
GO

ALTER PROCEDURE [dbo].[uspMyProcedure] 
    @variable1 INTEGER  
AS
BEGIN
   -- Stored procedure logic
END

당신은 왜 같은 간단한 길을 가지 않는가?

    IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE 'uspBlackListGetAll')
    BEGIN
         DROP PROCEDURE uspBlackListGetAll
    END
    GO

    CREATE Procedure uspBlackListGetAll

..........

@Geoff의 답변 외에 저장 프로시저, 뷰, 함수 및 트리거에 대한 문장이 있는 SQL 파일을 생성하는 간단한 도구를 만들었습니다.

MyDbUtils @ CodePlex 를 참조해 주세요.

궁금해!왜 질문 전체를 이렇게 쓰지 않는 거죠?

GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end

GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID       int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end

Go
create procedure test
as
begin 
select * from tblstudent
end

처음 2개의 프로시저가 이미 존재하는 것을 알고 있습니다.SQL은 처음 2개의 프로시저의 에러를 발생시킵니다만, 그래도 마지막 프로시저를 만듭니다.이것은 이미 존재하는 것을 관리하는 것입니다.이것은 항상 모든 클라이언트에 대해서 하고 있습니다.

프로시저명'이 존재하지 않는 경우 () 절차를 시작합니다.끝.

언급URL : https://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it