可以使用相同的名称来创建标量函数和存储过程吗?

huangapple go评论50阅读模式
英文:

Can I have a scalar function and a stored procedure with the same name?

问题

我们使用存储过程来生成一个内部编号(通过OUTPUT关键字访问)。

由于您不能在语句内部使用存储过程,我认为创建一个函数,该函数只是调用存储过程并返回OUTPUT值,可能是一个不错的主意,这样我可以直接在语句中使用它。

在一个数据库中是否可以给存储过程和标量函数取相同的名称,这个主意可行吗?

英文:

We use a stored procedure to generate an internal number (accessible via the OUTPUT keyword).

As you cannot use the stored procedure inside a statement, I thought it would be a good idea to just create a function which just calls the stored procedure with its parameters and returns the OUTPUT value. So I could directly use it for statements.

Is it possible to name a stored procedure and a scalar function the same in one database, and is this a good idea?

答案1

得分: 1

无法简单回答这个问题(感谢Martin):不,我不能。

引发的错误消息是错误2714("数据库中已经存在一个名为'Foo'的对象")。

英文:

To simply answer the question (thanks to Martin):
No, I can't.

The error message raised is Error 2714 ("There is already an object named 'Foo' in the database.").

答案2

得分: 0

一个数据库中任何关系对象的完整名称都有两个部分:

  1. 模式名称,实际上是数据库内的容器
  2. 对象名称,相对于模式是唯一的名称

作为演示,您可以执行:

CREATE SCHEMA S_SQL;
GO

CREATE USER U_SQL WITHOUT LOGIN WITH DEFAULT_SCHEMA = S_SQL;
GO

GRANT CONTROL TO U_SQL;
GO

EXECUTE AS USER = 'U_SQL';
GO

CREATE FUNCTION F_FOO()
RETURNS CHAR(3)
AS
BEGIN 
   RETURN 'foo';
END;
GO

REVERT;
GO

CREATE PROCEDURE F_FOO
AS
RETURN 'oof';
GO

SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES;

你将拥有相同的对象名称,但在两个不同的模式中...

英文:

A complete name of any relational object in a database is a name in two parts :

  1. the schema name, that is in fact a container inside the database
  2. the object_name, that is an unique name relative to the schema

As a demo you can do :

CREATE SCHEMA S_SQL;
GO

CREATE USER U_SQL WITHOUT LOGIN WITH DEFAULT_SCHEMA = S_SQL;
GO

GRANT CONTROL TO U_SQL;
GO

EXECUTE AS USER = 'U_SQL';
GO

CREATE FUNCTION F_FOO()
RETURNS CHAR(3)
AS
BEGIN 
   RETURN 'foo';
END;
GO

REVERT;
GO

CREATE PROCEDURE F_FOO
AS
RETURN 'oof';
GO

SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES;

You will have the same object name, but in two differerent schemas...

huangapple
  • 本文由 发表于 2023年2月14日 21:29:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75448544.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定