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 ===================================
No comments:
Post a Comment