1

I'm having a temp procedure:

CREATE PROCEDURE 
#update_ListItemEntityNumberValueAndLocalizations(
    @modelPrefix NVARCHAR(255), 
    @definitionNeutralName NVARCHAR(255), 
    @listItemNeutralValue NVARCHAR(255),    
    @newNumberValue float,
    @listItemEnName NVARCHAR(255),
    @listItemDeName NVARCHAR(255))

In this procedure there is the following if statement:

if(@listItemEnName is not null)

And at this line I get the following error:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the is not operation.

Does anyone know why this happens and how I can avoid it?

UPDATE: Database collation is SQL_Latin1_General_CP1_CI_AS

Why does a "is null" needs the collation?

Is there a way to cast the null or set the collation of the parameter?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Dominik Kirschenhofer
  • 1,205
  • 1
  • 13
  • 27
  • http://stackoverflow.com/questions/1607560/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and – Forte L. Oct 01 '12 at 20:00
  • I have found this also during my research but i do not realy know how this should help me... i dont have any table or column, just a parameter =/ – Dominik Kirschenhofer Oct 01 '12 at 20:04
  • @Dominik_Kirschenhofer Check rest of code for collation conflicts, the line number of the error is probably incorrct – TFD Oct 01 '12 at 20:12

1 Answers1

3

Use an explicit collate clause

if(@listItemEnName COLLATE Latin1_General_CI_AS is not null)

Or alternatively dependant upon what the stored procedure does switching the context to USE tempdb; then creating the temporary stored procedure and then switching the context back to your original database might work as below.

You are creating a temporary stored procedure so the parameter will be regarded as having the collation of tempdb. However tempdb must have a different collation from your user database.

As far as I can tell from experimentation when the stored procedure is first created it is bound to the database context in use (even if it is later ALTER-ed from a different database context).

. e.g. I am on a case sensitive collation but if I create the following procedure in a case insensitive database

CREATE PROC #Foo2 
AS
IF 'a' = 'A'
    PRINT 'Yes'
SELECT *
FROM sys.database_files

No matter what database I run it from it or if I alter it when USE-ing a different database it continues to print "Yes" and return information about the original database's files.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845