问题描述
我需要一些简单的 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 部分中创建过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!