We help IT Professionals succeed at work.

Dynamic SQL - with coalesce

akoIT
akoIT asked
on
Hi Expert,

Please help me find out the best solution for my query. I have dynamic sql with coalesce in where clause but it did not function well.

SET @sql = 'SELECT * FROM tbl1 WHERE ID = COALESCE(''' + @ID + ''', ID)'
EXEC(@sql)

and when I run this SP and i call

Exec sp_GetID @ID  = null,

it did not return a value.

But when i tried something like this

SELECT * FROM tbl1 WHERE ID = COALESCE(@ID , ID)

it works fine as expected.
Comment
Watch Question

Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

You need to put something into that variable, if not, you're putting nothing :)
SET @sql = 'SELECT * FROM tbl1 WHERE ID = COALESCE(' + COALESCE(@ID,'NULL') + ', ID)'
EXEC(@sql)

Open in new window


If you have NULL in @ID, inside you pass NULL literal which is interpreted as NULL keyword.

Regards,
Daniel
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
You need to use like below-

DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Id1 AS VARCHAR(10) = 'NULL'
SET @sql = ' SELECT * FROM [dbo].[ACCOUNT] WHERE [ACCOUNT_ID] = COALESCE(' + @Id1 + ', [ACCOUNT_ID]) '
EXEC(@sql)

Open in new window


output

/*------------------------
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Id1 AS VARCHAR(10) = 'NULL'
SET @sql = ' SELECT * FROM [dbo].[ACCOUNT] WHERE [ACCOUNT_ID] = COALESCE(' + @Id1 + ', [ACCOUNT_ID]) '
EXEC(@sql)
------------------------*/
ACCOUNT_ID  DOORS
----------- ------------
2           4
19          2
49          6
66          NULL

(4 row(s) affected)

Open in new window


Your Query

SET @ID = 'NULL'
SET @sql = 'SELECT * FROM tbl1 WHERE ID = COALESCE(' + @ID  + ', ID)'
EXEC(@sql)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Daniel_PL suggestion is not working for me. Daniel_PL did u add anything else.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

Yupp it is working as supposed. We can discuss whether it is desirable but it works as in question.
By adding COALESCE outside variable you always add value. Without that you may add NULL which is an empty string. In SQL Server by default CONCAT NULL yields NULL :)

PS C:\Users\Administrator> sqlcmd -S . -E
1> DECLARE @SQL NVARCHAR(MAX);
2> DECLARE @ID VARCHAR(10);
3> SET @sql = 'SELECT * FROM sys.objects WHERE object_id = COALESCE(' + COALESCE(@ID,'NULL') + ', object_id)';
4> PRINT (@SQL);
5> EXEC(@sql);
6> GO
SELECT * FROM sys.objects WHERE object_id = COALESCE(NULL, object_id)
name
         object_id   principal_id schema_id   parent_object_id type type_desc
         create_date             modify_date             is_ms_shipped is_published is_schema_published
------------------------------------------------------------------------------------------------------------------------
-------- ----------- ------------ ----------- ---------------- ---- ----------------------------------------------------
-------- ----------------------- ----------------------- ------------- ------------ -------------------
sysrscols
                   3         NULL           4                0 S    SYSTEM_TABLE
         2016-04-30 00:44:19.797 2016-04-30 00:44:19.810             1            0                   0
sysrowsets
                   5         NULL           4                0 S    SYSTEM_TABLE
         2009-04-13 12:59:05.513 2016-04-30 00:44:20.530             1            0                   0
sysclones
                   6         NULL           4                0 S    SYSTEM_TABLE
         2016-04-30 00:44:20.343 2016-04-30 00:44:20.353             1            0                   0
sysallocunits

Open in new window

ste5anSenior Developer

Commented:
The COALSECE() approach is used for building the SQL string. It is later not part of the resulting SQL statement:

DECLARE @Numeric INT;
DECLARE @String NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);

-- CASE 1 - NUMERIC, has value
SET @Numeric = 123;
SET @Sql = 'SELECT * FROM tbl1' + COALESCE(' WHERE ID = ' + CAST(@Numeric AS NVARCHAR(255)) + ';', ';');
SELECT 1 ,
       @Sql;

-- CASE 2 - NUMERIC, no value
SET @Numeric = NULL;
SET @Sql = 'SELECT * FROM tbl1' + COALESCE(' WHERE ID = ' + CAST(@Numeric AS NVARCHAR(255)) + ';', ';');
SELECT 2 ,
       @Sql;

-- CASE 3 - STRING, has value
SET @String = 'D''oh!';
SET @Sql = 'SELECT * FROM tbl1' + COALESCE(' WHERE TextCol = ''' + REPLACE(@String, '''', '''''') + ''';', ';');
SELECT 3 ,
       @Sql;

-- CASE 4 - STRING, no value
SET @String = NULL;
SET @Sql = 'SELECT * FROM tbl1' + COALESCE(' WHERE TextCol = ''' + REPLACE(@String, '''', '''''') + ''';', ';');
SELECT 4 ,
       @Sql;

-- CASE 5 - STRING, no value, IS NULL
SET @String = NULL;
SET @Sql = 'SELECT * FROM tbl1 WHERE TextCol ' + COALESCE('= ''' + REPLACE(@String, '''', '''''') + ''';', 'IS NULL;');
SELECT 5 ,
       @Sql;

Open in new window


Capture.PNG

Author

Commented:
Hi all, both suggestions are not woking in me.

the message is: Command(s) completed successfully but no result set being displayed.
ste5anSenior Developer

Commented:
btw, what's wrong with static SQL?

SELECT * FROM tbl1 WHERE ID = @ID OR @ID IS NULL;

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@author-

U need to pass null in single quotes.
Daniel_PLDB Expert/Architect
Top Expert 2011

Commented:
Hi,

The COALSECE() approach is used for building the SQL string. It is later not part of the resulting SQL
In an original question it is a part of built string, however I haven't discussed that. You're completely right, it should be used to build a dynamic sql string not be a part of it.

Again, good point on static SQL approach.

Regards,
Daniel
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
@Author - Check this statement.

ISNULL(@Id1,'NULL')

Working Sample.

/*------------------------
DECLARE @SQL AS VARCHAR(MAX)
DECLARE @Id1 AS VARCHAR(10) = NULL
SET @sql = ' SELECT * FROM [dbo].[ACCOUNT] WHERE [ACCOUNT_ID] = COALESCE(' + ISNULL(@Id1,'NULL') + ', [ACCOUNT_ID]) '
EXEC(@sql)
------------------------*/
ACCOUNT_ID  DOORS
----------- ------------
2           4
19          2
49          6
66          NULL

(4 row(s) affected)

Open in new window

Author

Commented:
Hi Pawan, I got invalid column on your example. PS, I have a temp table inside my SP.
ste5anSenior Developer

Commented:
@akoIT, @Pawan:

There is usually no sense in using dynamic SQL and COALESCE in the resulting SQL statement....

COALESCE() is used in this context for building correct dynamic SQL. Consider this simple fragment (@ID is NVARCHAR)

'SELECT * FROM tableName WHERE ID =' + @ID

Open in new window

When @ID is NULL then the entire string is NULL. This is the case COALESCE should handle:

'SELECT * FROM tableName' + COALESCE(' WHERE ID =' + @ID, '')

Open in new window


It replaces the NULL result string with a empty string or whatever is needed in the actual snippet.

But in the actual case, a catch-all is good enough:

SELECT * FROM tbl1 WHERE ID = @ID OR @ID IS NULL;

Open in new window


which is normally better than

SELECT * FROM tbl1 WHERE ID = COALESCE(@ID, ID);

Open in new window

Author

Commented:
Hi ste5an,

the reason I use the dynamic sql is to handle connection from different table with same fields. and I use COALESCE to filter the result base on users criteria.


this is my original code.

SELECT * INTO #EmpDetails FROM table1


SELECT field1, field2, field3 .... FROM #EmpDetails
WHERE isActive = COALESCE(@ACTIVE, isActive )

this is working, but when i transfer this into dynamic query, coalesce is not good.

`
Senior Developer
Commented:
Yup, I understood this. But you need to understand, that when using dynamic SQL COALESCE is no longer necessary. Cause you shift this to the dynamic SQL building part.

In short you want:

SELECT * FROM tableA WHERE isActive = COALESCE(@ACTIVE, isActive );
-- or
SELECT * FROM tableB WHERE isActive = COALESCE(@ACTIVE, isActive );

Open in new window


depending on your parameters. So its simply

DECLARE @ActiveBit BIT;
DECLARE @TableName sysname = 'tableName';
DECLARE @Sql NVARCHAR(MAX);

-- Case 1
SET @ActiveBit = NULL;
SET @Sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + COALESCE(' WHERE isActive = ' + CAST(@ActiveBit AS NVARCHAR(255)), '');
SELECT @Sql;

-- Case 2
SET @ActiveBit = 1;
SET @Sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + COALESCE(' WHERE isActive = ' + CAST(@ActiveBit AS NVARCHAR(255)), '');
SELECT @Sql;

Open in new window


Cause  ID = COALESCE(@ID , ID) is a tautology for @ID equals NULL. Thus we just remove the entire predicate.

p.s. if you really insist on using COALESCE in the final statemne:

DECLARE @ActiveBit BIT;
DECLARE @TableName sysname = 'tableName';
DECLARE @Sql NVARCHAR(MAX);

-- Case 1
SET @ActiveBit = NULL;
SET @Sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE isActive = COALESCE(' + COALESCE(CAST(@ActiveBit AS NVARCHAR(255)), 'NULL') + ', isActive);';
SELECT @Sql;

-- Case 1
SET @ActiveBit = 1;
SET @Sql = 'SELECT * FROM ' + QUOTENAME(@TableName) + ' WHERE isActive = COALESCE(' + COALESCE(CAST(@ActiveBit AS NVARCHAR(255)), 'NULL') + ', isActive);';
SELECT @Sql;

Open in new window