Dynamic SQL - with coalesce

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.
akoITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
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
0
Pawan KumarDatabase ExpertCommented:
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

0
Pawan KumarDatabase ExpertCommented:
Daniel_PL suggestion is not working for me. Daniel_PL did u add anything else.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Daniel_PLDB Expert/ArchitectCommented:
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

0
ste5anSenior DeveloperCommented:
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
0
akoITAuthor Commented:
Hi all, both suggestions are not woking in me.

the message is: Command(s) completed successfully but no result set being displayed.
0
ste5anSenior DeveloperCommented:
btw, what's wrong with static SQL?

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

Open in new window

1
Pawan KumarDatabase ExpertCommented:
@author-

U need to pass null in single quotes.
0
Daniel_PLDB Expert/ArchitectCommented:
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
0
Pawan KumarDatabase ExpertCommented:
@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

0
akoITAuthor Commented:
Hi Pawan, I got invalid column on your example. PS, I have a temp table inside my SP.
0
ste5anSenior DeveloperCommented:
@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

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

`
0
ste5anSenior DeveloperCommented:
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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.