What can be the limitation of "EXECUTE…WITH RESULT SETS"?

 Posted by Niladri.Biswas on 5/16/2013 | Category: Sql Server Interview questions | Views: 2961 | Points: 40
Answer:

We cannot return selected columns. The number of columns has to be same as that of the result set. For example, if we write something as under

EXEC Usp_FetchRecords 


WITH RESULT SETS

(
( [Emp Id] int,

[Phone Number] varchar(50)

)

)


The engine will report the below error

Msg 11537, Level 16, State 1, Procedure Usp_FetchRecords, Line 5 EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set number 1, but the statement sent 3 column(s) at run time.


The Usp_FetchRecords is as under

CREATE PROCEDURE [dbo].[Usp_ModifiedFetchRecords]

AS
BEGIN

Select
Id
,Name
,PhoneNumber
From dbo.tbl_Test;

Select
Id
,Name
From dbo.tbl_Test
Where PhoneNumber % 2 = 0
END


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response