Why is the Set command empty
I'm new to T-SQL and wanted to know why the following works and doesn't throw errors either:
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 operator that returns NO rows.
Then I expected @b to have the line "ALTER TABLE abc DROP CONSTRAINT", but when debugging it is empty. This is what I am confused about. Why is this happening?
I am using SQL Server Express 2008.
a source to share
whenever you concatenate strings you must guard against zeros, because whenever you concatenate a nullable string, the resulting string is null:
DECLARE @NullValue varchar(5)
SET @NullValue=null --not necessary but to make the point
SELECT 'Hello World'+@NullValue
exit:
------------
NULL
(1 row(s) affected)
zero protection:
DECLARE @NullValue varchar(5)
SET @NullValue=null --not necessary but to make the point
SELECT 'Hello World'+ISNULL(@NullValue,'')
exit:
------------
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 make sure you get what you need:
SELECT 'Hello World'+ISNULL(@YourValueHere,'')
a source to share
There are several questions:
1) @a comes out as null, so the @b assignment does not work as you expected. Make a validation check for @a before trying to use it and doing @b. As for the blank value, make sure your complex query is correct.
2) @b must be greater than @a so that it can hold the ALTER TABLE command plus whatever goes into @a. You currently have @b half the size of @a.
a source to share