Case sensitive variable names in SQL Server? [clos

When I execute this format of SQL command: SP_HELPTEXT Sproc1 . The result set will display Could not find stored procedure 'SP_HELPTEXT'. But if i will replace the SQL command to lower case like sp_helptext Sproc1 , it definitely displays the content of Sproc1.

Im using the Sproc1 in my program and when the program executes Sproc1 it will return a message:

Must declare the variable '@Variable1'.

Although I've already declared that specific variable.

I have a hint that the issue is related to collation, case-sensitive or insensitive settings. Does anybody know how to resolve ?

Another situation where case sensitive variable names appear:

CREATE PROCEDURE Foo @customerID int AS
PRINT @customerId

You have a case sensitive server collation.

Your database has a (as you have shown) a case insensitive collation but when you have a case issue with variables it is the server collation that matters.

The same goes for sp_helptext which is a stored procedure defined in database master with lowercase. So when you call SP_HELPTEXT it is not found.

To fix your stored procedure to work in a case sensitive server collation you have to make sure that every reference to the variable @Variable1 is exactly that. Not @variable1 or @VARIABLE1.

Use this to check what server collation you have.

SELECT SERVERPROPERTY('collation');

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.