Sql Server Stored Procedure Case Sensitive?

I have a case sensitive SERVER (SQL_Latin1_General_CP1_CS_AS) but the Database is Case insensitive (SQL_Latin1_General_CP1_CI_AS). If I try to create the following stored procedure

Solution 1:

You are right. Database collation does not control variables name case sensitivity - server collation does.

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).

From the SQL Server Books Online:

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.

• The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

  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)

See also

• MSDN forums: Why are my SP's throwing a case error when pushing to a db using BIN collation?
• Case sensitive variables in SQL Server
• Case sensitive variable names in SQL Server?