COALESCE causing COLLATE error in Dynamic SQL

1

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
2

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;
3

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 ...

4

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
5

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