How to create variable/identifier with collate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create variable/identifier with collate

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-05-05 : 09:41:51

Hi,

From SQL Server 2005 BOL
"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. The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the instance."

We have Binary SQL Server collation at instance level and
SQL_Latin1_General_CP1_CI_AS at database and I am trying to create a
variable inside stored procedure with collate clause with following syntax:

declare @d varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS

and getting error:
" Incorrect syntax near the keyword 'COLLATE'."

What is the correct syntax to create variable within block/sp using
particular collation.

apodemus
Starting Member

30 Posts

Posted - 2010-05-05 : 10:00:46

you can use COLLATE clause when you will use that variable (select * from table where col1 = @d COLLATE SQL_Latin1_General_CP1_CI_AS)

apodemus

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources