Why is Set Command is blank

Question

I am new to T-SQL and wanted to know why the following works and does not raise and error:

I have :

DECLARE @aVARCHAR(200), @b VARCHAR(100) 
SET @a = (Some complicated SELECT Statement) 
SET @b = 'ALTER TABLE abc DROP CONSTRAINT ' + @a; <-------- expected it to contain string.
Exec(@b);

The first set has a complex select statement which returns NO rows.

I then expected @b to have the string 'ALTER TABLE abc DROP CONSTRAINT ' BUT it is empty when debugging. This is what I found confusing. Why is this happening?

I am using SQL Server Express 2008.

Answer 1

If @a is null, then anything concatenated to it will also be null. Do something like

isnull(@a,'') + 'rest of the string'

Answer 2

There's a few issues:

1) @a is coming out as null, so the @b assignment is not working as you expected. Do a validation check on @a before trying to use it and executing @b. As to why it's blank, make sure you have your complex query bit correct.

2) @b needs to be bigger than @a to ensure it can hold the ALTER TABLE command plus whatever goes in to @a. Currently you have @b at half the size of @a.

Answer 3

whenever you concatenate strings you must protect against nulls because whenever you concatenate a string with null, the resulting string is null:

DECLARE @NullValue varchar(5)
SET @NullValue=null  --not necessary but to make the point
SELECT 'Hello World'+@NullValue

output:

------------
NULL

(1 row(s) affected)

protect against nulls:

DECLARE @NullValue varchar(5)
SET @NullValue=null  --not necessary but to make the point
SELECT 'Hello World'+ISNULL(@NullValue,'')

output:

------------
Hello World

(1 row(s) affected)

another example:

SELECT 'Hello World'+@YourValueHere

what will be displayed? who knows, if @YourValueHere is NULL, then nothing. use this instead to be sure you get what you are after:

SELECT 'Hello World'+ISNULL(@YourValueHere,'')