Dynamic SQL - with coalesce

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)

Select allOpen in new window

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

Regards,
Daniel

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)

Select allOpen 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)

Select allOpen in new window

Your Query

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

Select allOpen in new window

Daniel_PL suggestion is not working for me. Daniel_PL did u add anything else.

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

Select allOpen in new window

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;

Select allOpen in new window

Hi all, both suggestions are not woking in me.

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

btw, what's wrong with static SQL?

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

Select allOpen in new window

@author-

U need to pass null in single quotes.

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

@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)

Select allOpen in new window

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

@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

Select allOpen 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, '')

Select allOpen 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;

Select allOpen in new window

which is normally better than

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

Select allOpen in new window

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.

`