CODEWITHSUNDEEP
sql-servert-sql
user3010406
user3010406

Reputation: 579

ISNULL() On Value, But If Not Null, Put Quotes

I am building dynamic SQL statements. I am checking for null values, and if the value is not null, putting single quotes around the parameter value. In the below @TEST_GU is a string parameter.

BEGIN TRAN
DECLARE @SQL nvarchar(max)
SELECT @SQL = 'UPDATE [THIS IS A TABLE]
SET [TEST_GU]=' + '''' + ISNULL(@TEST_GU,'') + ''''+', 
+ ' SELECT [TEST_GU] FROM 
[THIS IS A TABLE]
WHERE [TEST_GU] =' + '''' + ISNULL(@TEST_GU,'') + '''' +''
PRINT LEN(@SQL)
EXEC (@SQL)
COMMIT

This wont work because if its null, it ends up putting quotes around the empty value, so makes the whole statement become unformatted. So my question is, in the above format, is it possible to check for null values, if null use the second argument of the ISNULL method (in this case, an empty ''). If it is not null, put the parameter value in single quotes.

Upvotes: 0

Views: 2111

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Just put the quotes inside the isnull():

SELECT @SQL = 'UPDATE [THIS IS A TABLE]
SET [TEST_GU]=' + '''' + ISNULL(''''+@TEST_GU+'''','') + ''''+', 
+ ' SELECT [TEST_GU] FROM 
[THIS IS A TABLE]

The concatenation will return NULL if the value is NULL, so it still does what you want.

Upvotes: 2

Related Questions