SQL Server 存储过程区分大小写? [英] SQL Server stored procedure case sensitive?
问题描述
我有一个区分大小写的服务器 (SQL_Latin1_General_CP1_CS_AS)，但数据库不区分大小写 (SQL_Latin1_General_CP1_CI_AS).
I have a case sensitive SERVER (SQL_Latin1_General_CP1_CS_AS) but the Database is Case insensitive (SQL_Latin1_General_CP1_CI_AS).
如果我尝试在数据库上创建以下存储过程，则会收到错误必须声明标量变量@test".
If I try to create the following stored procedure on the database, I get the error "Must declare the scalar variable "@test"."
CREATE PROCEDURE [dbo].[sp_Test] (@TEST int) as
begin
SELECT @test
end
GO
但正如我所说，数据库本身不区分大小写.我假设这被记录在某个地方，存储过程遵循服务器的敏感性，但我在任何地方都找不到参考.谁能指出我在哪里可以找到有关此的一些文档?(是的，我试过谷歌，但我没有找到任何东西)
But as I stated the database itself is not case sensitive. Im assuming this is documented somewhere that stored procedures follow the sensitivity of the server but I cannot find a reference anywhere. Can anyone point me to where I would find some docs about this? (Yes I tried google, but im not finding anything)
推荐答案
你说得对.数据库整理不控制变量名称区分大小写 - 服务器 整理可以.
You are right. Database collation does not control variables name case sensitivity - server collation does.
任何其他对象名称(例如表、视图、列)都遵循数据库整理规则.在您的情况下，这意味着不区分大小写，因为您的数据库是CI(不区分大小写).
Any other object name (e.g. table, view, column) follows database collation rules. In your situation, that means case insensitive, since your database is CI (case insensitive).
来自 SQL Server 联机丛书:
标识符的排序规则取决于定义它的级别.
COLLATE (Transact-SQL)
The collation of an identifier depends on the level at which it is defined.
- 实例级对象的标识符，例如登录名和数据库名称，被分配了实例的默认排序规则.
数据库中对象的标识符，例如表、视图和列名，被分配了数据库的默认排序规则.
- Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance.
Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database.
例如，两个名称仅大小写不同的表可以在区分大小写的数据库中创建，但不能在不区分大小写的数据库中创建.有关详细信息，请参阅数据库标识符.
For example, two tables with names different only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. For more information, see Database Identifiers.
变量、GOTO标签、临时存储过程和临时表的标识符是在服务器实例的默认排序规则中.
The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.
当连接上下文与一个数据库相关联时，可以创建变量、GOTO 标签、临时存储过程和临时表，然后在上下文切换到另一个数据库时引用.
Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database.
您可以使用以下方法检查您的服务器排序规则:
You can check your server collation using:
SELECT SERVERPROPERTY('collation');
SQL_Latin1_General_CP1_CI_AS
(1 row(s) affected)
另见
- MSDN 论坛:为什么我的 SP 在使用 BIN 排序规则推送到数据库时会抛出大小写错误?
- SQL Server 中的区分大小写的变量
- SQL Server 中的变量名是否区分大小写?
这篇关于SQL Server 存储过程区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
