在 IF 部分中创建过程 [英] Creating procedure inside IF section

问题描述

我需要一些简单的 SQL 代码帮助:

I need some help with simple SQL code:

DECLARE @procExists int
SET @procExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Table_Exists' AND ROUTINE_TYPE = 'PROCEDURE')
IF NOT @procExists > 0 
BEGIN
    -- test query
    -- SELECT 'Something' = @procExists;

    -- error throwing code
    -- CREATE PROCEDURE Table_Exists
    --     @schemaName varchar(50),
    --     @tableName varchar(50)
    -- AS
    --     RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
END

上面的简单代码:
- 声明一个 int 变量
- 检查过程 dbo.Table_Exists 是否存在
- 如果不存在则创建它

The simple code above:
- declares an int variable
- checks if procedure dbo.Table_Exists exists
- IF NOT exists it creates it

我的问题是这个错误信息:

My problem is this error information:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@schemaName".

我不知道为什么，但是..
- 当我执行CREATE PROCEDURE"主体时单独它可以工作
- 当我执行整个 IF 部分 排除 'CREATE PROCEDURE' 正文时，简单的查询有效
- 当我执行整个 IF 部分包括CREATE PROCEDURE"主体时，会抛出错误

I don't know why, but..
- when i execute 'CREATE PROCEDURE' body alone it works
- when i execute whole IF section excluding 'CREATE PROCEDURE' body, simple query works
- when i execute whole IF section including 'CREATE PROCEDURE' body, error is thrown

我错过了什么?

推荐答案

CREATE PROCEDURE 必须在它自己的批处理中

CREATE PROCEDURE has to be in it's own batch

所以，动态 SQL 是一种方式:

So, dynamic SQL is one way:

IF OBJECT_ID('Table_Exists') IS NULL
BEGIN
    EXEC ('CREATE PROCEDURE Table_Exists
         @schemaName varchar(50),
         @tableName varchar(50)
     AS
         RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
')
END

或先删除

IF OBJECT_ID('Table_Exists') IS NOT NULL
  DROP PROC Table_Exists
GO
CREATE PROCEDURE Table_Exists
         @schemaName varchar(50),
         @tableName varchar(50)
     AS
         RETURN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schemaName AND TABLE_NAME = @tableName)
GO

注意使用 OBJECT_ID 来查看 proc 是否存在.

Note the use of OBJECT_ID to see if the proc exists.

这篇关于在 IF 部分中创建过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！