top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is a Result Set? when is it created?

+2 votes
239 views
What is a Result Set? when is it created?
posted Nov 18, 2014 by Archana

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

Result Set clause with the EXECUTE statement, which lets you redefine the name and data types of the columns being returned from the stored procedure. This comes in very handy when you want to insert the records coming in a result set to a temporary table with a specific column name and data type and you don’t want to rely on what is being returned by the stored procedure.

The WITH RESULT SETS clause can also be used with a stored procedure, which returns multiple result sets and for each result set you can define the column name and data types for each column separately.

Please note:

1.Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
2.The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
3.The number of columns being returned as part of result set cannot be changed.

CREATE PROCEDURE SampleSP
AS
SELECT 1 AS Col1, 2 AS Col2
UNION
SELECT 11, 22
GO
CREATE TABLE #TempTable (Col1 INT, Col2 INT)
GO
INSERT INTO #TempTable
EXEC SampleSP
GO

SELECT *
FROM #TempTable
GO
answer Nov 19, 2014 by Manikandan J
0 votes

The result set can be defined as a logical set of rows that meet the query submitted to
the server process. The only way to reference this object is through the cursor handle.
this is created when the Cursor is opened.

answer Nov 19, 2014 by Arun Gowda
...