SQL Server Portal

EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null is one of the new error messages noticed in SQL Server 2012 and above. This error message is due to the invalid use of new feature EXEC WITH RESULT SETS shipped with SQL Server 2012.

Lets discuss this in detail:
Message Number: 11553

Severity : 16

Error Message: EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #%d in result set #%d, and the corresponding value sent at run time was null.

Error Generation:
Let me create a sample stored procedure to generate this error.

Use AdventureWorks2014
GO
--DROP PROCEDURE usp_Sample
--GO
CREATE PROCEDURE usp_Sample
AS
SELECT  
  [BusinessEntityID]
, [FirstName]
, [MiddleName]
, [LastName]
FROM [HumanResources].vEmployee ORDER BY BusinessEntityID 
GO
EXEC usp_Sample
GO

You can see that the above stored procedure is executed successfully and it has ONE result set.

Lets try to execute it using WITH RESULT SETS clause.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NOT NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT

Ooops…… I am unable to execute it properly. It returned the error message.

Resolution:
Why this error ? Because, I specifically mentioned in the RESULT SETS that none of the columns should return NULL and I did not realize that stored procedures return some NULL values in the middle name column and due to this I got this error. So, when you mention any column as a NOT NULL in RESULT SETS, please make sure that the stored procedure must NOT return NULL VALUE for that particular column. Lets re-write the RESULT SET and this time we need to mention, middle name column is NULL as shown below.

USE AdventureWorks2014 
GO
EXEC usp_Sample
WITH RESULT SETS
(
( [BusinessEntityID] int NOT NULL,
  [First Name] Name NOT NULL,
  [MiddleName] Name NULL,
  [LastName] Name NOT NULL
));
GO
--OUTPUT

Conclusion :
Remember, whenever you use any stored procedure using WITH RESULT SETS clause, along with the nullable type, make sure that the data that comes from stored procedure should match with the nullable type. In other words, if you define any column in RESULT SETS clause as NOT NULL column then make sure that stored procedure MUST NOT return NULL value in that particular column to avoid such errors.

Read Full Post »