Dynamic SQL and NULL Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic SQL and NULL Parameters

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-02-13 : 13:28:06

I have a large dynamic sql in order to insert dynamic operators such as less than and greater than.

Most parameters are set = NULL because in the where clause it's needed for a proper search such as:


WHERE cost = @cost OR @cost IS NULL

The string will not work if the parameter is null such as:


DECLARE @test INT = NULL
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT SomeCol FROM myTable WHERE someID = ' + CONVERT(VARCHAR(100), @test)
print @sql

But if @test is set to 1, it prints just fine and will execute using exec (@sql)

Is there a way around these null values to build a dynamic sql?

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-13 : 13:36:42

Two ways:

1. Resolve the null before building your dynamic sql statement:

SET @test = ISNULL(@TEST, 'something other than null')

2. add an ISNULL expression in your dynamic sql

SET @sql = ' .... CONVERT(VARCHAR(100), ISNULL(@TEST, ''something other than null'') ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-13 : 13:39:47

You can either set CONCAT_NULL_YIELDS_NULL off, or you can use ISNULL/COALESCE to switch it to the empty string.

SET @sql = 'SELECT SomeCol FROM myTable WHERE someID = ' + CONVERT(VARCHAR(100), COALESCE(@test, ''))

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources