CODEWITHSUNDEEP
sql-serverstored-procedures
Kyle
Kyle

Reputation: 17687

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

Upvotes: 5

Views: 11154

Answers (1)

martin.malek
martin.malek

Reputation: 2218

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

Upvotes: 6

Related Questions