SQLTeam.com | Weblogs | Forums

COALESCE causing COLLATE error in Dynamic SQL

Not had this before, and took me a while to figure out, so posting in case it saves someone else ...

From Database1 run dynamic SQL on Database2, both databases have different default collation.

I am familiar with comparison of columns BETWEEN databases as needing consideration for Collation - and, indeed, between Tables & Columns WITHIN a database if they happen to have different collations, although that is very rare for us.

Also, comparing a char/varchar column to an @Variable has never needed collation ...

... but today I added a COALESCE to the @Variable and that blew up - presumably because COALESCE defaults to the current-database collation, rather than the collation of the database where the dynamic SQL was targetted:

USE MyDatabase1	-- Database Collation = SQL_Latin1_General_CP1_CI_AS
GO

DECLARE	@Schema sysname = 'dbo'

DECLARE	@strSQL	nvarchar(MAX)
SELECT	@strSQL = '
USE MyDatabase2;	-- Database Collation = Latin1_General_CI_AS

SELECT	TOP 1
	DB.database_id
FROM	sys.databases AS DB
	LEFT OUTER JOIN sys.schemas AS S
		 ON S.name = COALESCE(@Schema, ''dbo'') -- Raises Error unless COLLATE used
'

EXEC sp_ExecuteSQL
	@strSQL
	, N'@Schema	sysname'
	, @Schema	= @Schema

Try it like this...

USE MyDatabase1;	-- Database Collation = SQL_Latin1_General_CP1_CI_AS
GO

DECLARE	@Schema sysname = 'dbo';

DECLARE	@strSQL	nvarchar(MAX);
SELECT	@strSQL = N'
USE MyDatabase2;	-- Database Collation = Latin1_General_CI_AS

SELECT	TOP 1
	DB.database_id
FROM	sys.databases AS DB
	LEFT OUTER JOIN sys.schemas AS S
		 ON S.name = ''' + COALESCE(@Schema, N'dbo') + N''' -- Raises Error unless COLLATE used
';

EXEC sp_ExecuteSQL
	@strSQL
	, N'@Schema	sysname'
	, @Schema	= @Schema;

That's a thought, thanks. I'll need to double-up any embedded quotes (SQL Injection etc.), ... actually I probably ought to QuoteName() it anyway ...

My problem with it was that it took me so long to figure out where the problem was, as I was not believing that (what seemed to be)

WHERE SomeColumn =@SomeParameter

could ever have a COLLATE issue ... I know better now of course :slight_smile: ... blinking accident-waiting-to-happen if most databases have same collation ...

Well the other option, of course, would be to handle the NULL logic in the in the proc, before the dynamic sql...

USE MyDatabase1;	-- Database Collation = SQL_Latin1_General_CP1_CI_AS
GO

DECLARE	@Schema sysname = 'dbo';

SET @Schema = COALESCE(@Schema, 'dbo');  -- if the user NULLs the parameter, set it before the dynamic is declared...

DECLARE	@strSQL	nvarchar(MAX);
SELECT	@strSQL = '
USE MyDatabase2;	-- Database Collation = Latin1_General_CI_AS

SELECT	TOP 1
	DB.database_id
FROM	sys.databases AS DB
	LEFT OUTER JOIN sys.schemas AS S
		 ON S.name = @Schema -- Raises Error unless COLLATE used
';

EXEC sp_ExecuteSQL
	@strSQL
	, N'@Schema	sysname'
	, @Schema	= @Schema;
1 Like

Thanks, actually turns out you can do it in the Dynamic SQL too

USE MyDatabase1	-- Database Collation = SQL_Latin1_General_CP1_CI_AS
GO

DECLARE	@Schema sysname = 'dbo'

DECLARE	@strSQL	nvarchar(MAX)
SELECT	@strSQL = '
USE MyDatabase2	-- Database Collation = Latin1_General_CI_AS

SELECT @Schema = COALESCE(@Schema, ''dbo'') -- Assigning here avoids Collation Issue later

SELECT	TOP 1
	DB.database_id
FROM	sys.databases AS DB
	LEFT OUTER JOIN sys.schemas AS S
		 ON S.name = @Schema -- NO Collation Error Issue!!
'

EXEC sp_ExecuteSQL
	@strSQL
	, N'@Schema	sysname'
	, @Schema	= @Schema

P.S. SQL might make a better Query Plan too

1 Like