Dynamic SQL, NULL, and EXEC

I've been a fan of dynamic T-SQL for a long time. I ran into something new, though, that threw me for a loop. It started with a stored procedure that builds a dynamic query string, based on a dozen or so input parameters that are all optional. If an input parameter is NULL, it gets excluded from the query logic. Here's a typical example with three parameters:

CREATE PROCEDURE dbo.GetSomeData
 @Param1 INT,
 @Param2 BIGINT,
 @Param3 UNIQUEIDENTIFIER
AS
DECLARE @QUERY VARCHAR(8000);

SET @QUERY = 'SELECT blah, blah, blah' + CHAR(13) + CHAR(10) +
 'FROM TableX AS x '  + CHAR(13) + CHAR(10) +
 'JOIN TableY as y ON x.Xid = y.Xid '  + CHAR(13) + CHAR(10) +
 'WHERE 1 = 1 '  + CHAR(13) + CHAR(10)

IF @Param1 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param1 = ' + CAST(@Param1 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

IF @Param2 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param2 = ' + CAST(@Param2 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

IF @Param3 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param3 = ' + CAST(@Param3 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

EXEC (@QUERY);

As you may know, you have to take care when building the @QUERY string. If a single NULL value gets introduced into the string, the entire string becomes NULL and the EXEC() function returns nothing, much like this:

DECLARE @Name VARCHAR(32) = NULL;
DECLARE @QUERY VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar, ' + @Name + ' AS [Name];';

EXEC (@QUERY);
GO


Commands completed successfully.

The variation I recently encountered was just slightly different. There were separate variables for @SELECT, @FROM, and @WHERE. All three variables were concatenated and passed to the EXEC() function. Here's an example that uses two variables for simplicity:

DECLARE @SELECT VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar';
DECLARE @WHERE VARCHAR(8000) = ' WHERE 1 = 1';

EXEC (@SELECT + @WHERE);
GO

Foo         Bar
----------- -----------
1           2

(1 row affected)

So far, so good. Now for the twist. If one of the concatenated variables passed to EXEC() is NULL, it is ignored:

DECLARE @SELECT VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar';
DECLARE @WHERE VARCHAR(8000) = NULL;

EXEC (@SELECT + @WHERE);
GO

Foo         Bar
----------- -----------
1           2

(1 row affected)

In my opinion, the above shouldn't have worked. I can't explain it. This is the type of edge case mystery that might normally consume hours or days to solve. Thankfully, a debugging session helped me understand what was happening without wasting too much time.

In conclusion, I want to point out a few things that might be obvious to you. Dynamic T-SQL is difficult (duh!). Be careful with both NULLs and the EXEC() function. Lastly, and most importantly, the examples presented here are vulnerable to SQL injection attacks. Best practice is to parameterize queries with sp_executesql.