Feeds:
Posts
Comments

Posts Tagged ‘error’

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

Error number 11553.1.1

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
Msg 11553, Level 16, State 1, Procedure usp_Sample, Line 22
EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #3 in result set #1, and the corresponding value sent at run time was null.

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

Error number 11553.1.2

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

Error number 11553.1.3

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.

Advertisements

Read Full Post »