CODEWITHSUNDEEP
sql-server-2012
Thomas
Thomas

Reputation: 34198

How this execute statement works in SQL Server 2012

I got a snip of a new execute statement in SQL Server 2012. after looking into it I have some confusion. Please have a look

 EXEC sproc_GETSTUDENT 
 WITH RESULT SETS 
 ( 
      ( 
             STUDENTROLLNO  INT,
             NAMEOFSTUDENT  VARCHAR(50)
      ) 
 );

What does this do?

Does it execute a stored procedure and populate the data in result table?

Result is a temporary table or CTE because with keyword is used. We know we use with keyword at the time of CTE. So please explain what will happen when the above snip run. Thanks

Upvotes: 2

Views: 315

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

It is documented in EXECUTE (Transact-SQL) and is used to describe the result set.

In your case you will get two columns when you execute the statement. One int column called STUDENTROLLNO and one varchar(50) column called NAMEOFSTUDENT.

With this stored procedure:

CREATE PROCEDURE sproc_GETSTUDENT AS
SELECT 1 AS C1, 'Pelle' AS X1

You will get 

C1          X1
----------- -----
1           Pelle

if you execute the procedure using EXEC sproc_GETSTUDENT

But if you instead do 

EXEC sproc_GETSTUDENT 
 WITH RESULT SETS 
 ( 
      ( 
             STUDENTROLLNO  INT,
             NAMEOFSTUDENT  VARCHAR(50)
      ) 
 );

you get a result set like this.

STUDENTROLLNO NAMEOFSTUDENT
------------- --------------------------------------------------
1             Pelle

Upvotes: 3

Related Questions