CODEWITHSUNDEEP
sql-serverstored-procedures
Selrac
Selrac

Reputation: 2293

MSSQL collation conflict stored procedure

I have an error with a query like 

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CS_AS" in the equal to operation.

I know that I can resolve this error with this SQL statement:

ALTER TABLE [TableA]
ALTER COLUMN [ColumnX]
VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS;

The problem I have is that I have a stored procedure where I have added the SQL statement to alter the column and then the query I'm getting the error, but the alter statement don't seem to be executed. The code in the stored procedure is as follows:

ALTER TABLE [TableA]
ALTER COLUMN [ColumnX]
VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS; 

INSERT INTO [TableC] 
([ColumT] )
SELECT 
 [TableB].[Account]
FROM [TableA], [TableB]
WHERE  
 Left(TableA.[ColumnX],1)=TableB.[ColumnY];

If I have run the ALTER and INSERT SQL code independently, then the INSERT statement works. If I run them together in a stored procedure, then I get the error.

Why is the stored procedure not running the first ALTER SQL statement?

Upvotes: 0

Views: 7030

Answers (1)

TT.
TT.

Reputation: 16145

Either you change the table to have the proper collation by executing the alter statement outside the scope of a stored procedure (i.e. standalone). This only has to be done once.

Doing this in the stored procedure is not the right place. You would alter this table column each time the stored procedure is executed, which is not necessary. Also it can be problematic if you later decide this column has to change in another way; you would have to remember and change the stored procedure as well. That is not desirable.

Or, you specify the collation that is needed in the SQL statement

INSERT INTO [TableC] 
    ([ColumT] )
SELECT 
    [TableB].[Account]
FROM 
    [TableA] 
    INNER JOIN [TableB] ON
        LEFT(TableA.[ColumnX],1) COLLATE SQL_Latin1_General_CP1_CI_AS = TableB.[ColumnY];

Upvotes: 2

Related Questions