What’s New in T-SQL SQL Server 2012–Result Set


Lets Build a Scenario,

Imagine your need to Standardize your Stored procedure output for dynamic batching. as a matter of fact if the output of your Stored procedure is different from what you expect your query will fail.

So you need to standardize your output from stored procedure. to Do that lets welcome the new SQL Server 2012 T-SQL Feature Result Set.

Result Set will let the format the stored procedure output to map your requirements no matter how the data is formatted internally. Lets take a look using the following code sample

EXECUTE <batch_or_proc> WITH <result_sets_option>;

Sample Code for that is

EXECUTE GetEmployees WITH RESULT SETS(
(

EmployeeId INT,

EmployeeName VARCHAR(150)

)

)

Some notes to consider when using Result Set

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s