Articles → SQL SERVER → Enhanced Execute Statement With Result Sets In SQL Server

Enhanced Execute Statement With Result Sets In SQL Server

In this article, we will discuss the enhanced execute statement with result sets in the SQL server.

Purpose

When the stored procedure is executed, the data type of the result set is the same as returned by the stored procedure.

With the option with result sets, we can modify the data type of the columns of the result set returned by the stored procedure.

Sample

Consider the following sample table. In the next step, we will create a stored procedure to fetch the data from this table.

Click to Enlarge

Stored Procedure

Create the following stored procedure to fetch the data from the table.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[getPopulation]
As
SELECT [Id]
      ,[State]
      ,[In2011]
      ,[in2023]
      ,[Difference]
  FROM [gyansangrah].[dbo].[population]
GO

With Result Sets In Action

We can use the With Result Sets to change the data type of the result set.

exec getPopulation 
with result sets
((
   [Id] float,
   [State] varchar(200),
   [In2011] float,
   [in2023] float,
   [Difference] float


))

In the above code, we are changing the data type of columns from int to float.

Posted By -	Karan Gupta

Posted On -	Tuesday, October 24, 2023

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**