sp_executeSQL to the rescue

(a.k.a. my DEFAULT constraints aren’t working –NULL is being written to the DB.  Where are my default values?)

I encountered a bug in a stored procedure I’d written that took me a little longer to fix than it should have that led me to another aspect of SQL that I’d not used and thought was both cool and essential and worth a post.  Basically the stored procedure in question adds a row to the person table.  I fire the procedure from a PowerShell script and provide the variables as parameters.  The database is normalised (as best I could – I think I’ve got as far as 3NF) so I need to convert the string input into IDs which is where the stored procedure comes in.  Anyway to cut a long story short once I’ve done my validation I run the following:

BEGIN TRY
	INSERT INTO Person VALUES
	(
		@UniqueName,
		@AltUniqueName,
		@DisplayName,
		@teamID,
		@mgrID,
		@empID,
		@secID,
		@natID,
		@jobID
	);

	SET @rval = 1;
	RETURN;
END TRY

The problem is that some of those values can be NULL.  Or more specifically the user doesn’t input a value for job title (mapped to jobID) or employee type (mapped to empID) for example.  The bug was that the NULL was making it into the table.  This was initially unexpected as there are default constraints in place.  I assumed that supplying NULL via the table valued constructor would result in the value specified in the default constraint being used.  This isn’t the case however.  The ANSI standard behaviour is that a NOT NULL column with a DEFAULT constraint will not use the default if the column is specified in the INSERT statement.  That is, specifically providing NULL actually writes NULL into the tuple and does not utilise the DEFAULT constraint.

OK.  How do we fix this?  Dynamic SQL.  Specifically EXECUTE and sQLCommandString.  Here’s how I replaced the above to get the desired behaviour:

	DECLARE @sQLCommandString NVARCHAR(MAX) = N'INSERT INTO Person VALUES ( ' +
		'@UniqueName, @AltUniqueName, @DisplayName, @teamID, @mgrID, ' +
		CASE WHEN @empID IS NULL THEN 'DEFAULT' ELSE '@empID' END + ', ' +
		CASE WHEN @secID IS NULL THEN 'DEFAULT' ELSE '@secID' END + ', @natID, ' +
		CASE WHEN @jobID IS NULL THEN 'DEFAULT' ELSE '@jobID' END + ' );';

	DECLARE @paramDefinition NVARCHAR(MAX) = N'@UniqueName NVARCHAR(256), ' +
		'@AltUniqueName NVARCHAR(20), @DisplayName NVARCHAR(256), ' +
		'@teamID BIGINT, @mgrID BIGINT, @empID SMALLINT, @secID BIGINT, ' +
		'@natID BIGINT, @jobID BIGINT';

	BEGIN TRY
		EXECUTE sp_executeSQL @sQLCommandString, @paramDefinition,
			@UniqueName = @UniqueName,
			@AltUniqueName = @AltUniqueName,
			@DisplayName = @DisplayName,
			@teamID = @teamID,
			@mgrID = @mgrID,
			@empID = @empID,
			@secID = @secID,
			@natID = @natID,
			@jobID = @jobID;

		SET @rval = 1;
		RETURN;
	END TRY

I build a dynamic SQL statement and execute that.  What’s cool about sp_executesql is that it understands variables.  The above is a little lengthier than the original version but vastly better than a bunch of IF ELSE statements utilising IS NULL and IS NOT NULL to determine what values to input into the table valued constructor.

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in Off-topic, SQL Server, Troubleshooting and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s