Latest Post

Tuesday, 18 October 2016

How to Use "WITH RESULT SETS" Feature of SQL Server 2012

WITH RESULT SETS:-

WITH RESULT SETS  can be used to change the output of stored procedures by defining new column names, their type, and other sorts of operations when executing stored procedures.


Example :- below is the WITH RESULT SETS


--- Create Students table
CREATE TABLE [dbo].[Students]
(
  [Id]   [INT]   NOT NULL IDENTITY PRIMARY KEY,
  [Name]  NVARCHAR(100) NOT NULL,
  [Age]  INT  NOT NULL,
  [Address] NVARCHAR(500) NOT NULL,
)
GO

----- Insert records on Students table

INSERT INTO [dbo].[Students] (Name, Age, Address)
VALUES
('Alok Singh', 24,'INDIA'),
('Thomas desuja', 20,'USA')
GO

--Check and Create procedure to get the Student records 

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_GetStudents]')   AND type in (N'P', N'PC'))
       DROP PROCEDURE [dbo].[SP_GetStudents]
GO

CREATE PROCEDURE SP_GetStudents
AS
BEGIN
 SELECT Id, Name + ' - ' + ISNULL(CAST(Age AS varchar(10)), '') +' - '+ Address AS Name
 FROM dbo.Students
END
GO

----- Excutting the procedure
EXECUTE   SP_GetStudents
GO

RESULT:-

Id Name
==================================
1 Alok Singh - 24 - INDIA
2 Thomas desuja - 20 - USA

==================================

--- Here Using WITH RESULT SETS on procedure

EXECUTE   SP_GetStudents 
WITH RESULT SETS
(
       (
       StudentId INT,
       StudentDetails VARCHAR(500)
       ) 
)
GO

Result-

StudentId StudentDetails
===================================
1 Alok Singh - 24 - INDIA
2 Thomas desuja - 20 - USA

===================================