Programmability Enhancements for EXECUTE Statement

by Amol 24. February 2011 09:58

We all are familiar with EXECUTE statement in SQL Server. Below are some programmability enancement for EXECUTE statement.

Using WITH RESULT SET

 Execute stement can return resultset by using WITH RESULT SET argument.  You can specify your own aliases to return resultset. 

EXEC dbo.GetEmployeeDetails  100

WITH RESULT SETS

(

   ([Id] int NOT NULL,

    [First Name] nvarchar(50) NOT NULL,

    [Last Name] nvarchar(50) NOT NULL,

    [Manager Name] nvarchar(50) NOT NULL)

);

You can define multiple resultset as well using WITH RESULT SET clause. Following example demonstrate how to retrun multiple resultsets.

CREATE PROCEDURE [dbo].[GetEmployeeDetails]

(     @EmployeeId INT )

AS

BEGIN

 

    -- First resultset

      SELECT     

            EmployeeId,      

            EmployeeName

      FROM [dbo].[Employee]

      WHERE EmployeeId = @EmployeeId

 

             -- Second resultset

 

      SELECT

            mgr.EmployeeId AS ManagerId,

            mgr.EmployeeName AS ManagerName

      FROM [dbo].[Employee] emp

      LEFT JOIN [dbo].[Employee] mgr

ON (emp.ManagerId = mgr.EmployeeId)

      WHERE emp.EmployeeId = @EmployeeId       

 

END

GO

-- Execute the procedure

EXECECUTE [dbo].[GetEmployeeDetails] 100

WITH RESULT SETS

(

    -- first result set definition starts here

    (EmployeeId int,  

    Name nvarchar(50))

    , -- comma separates result set definitions

    -- second result set definition starts here

    (ManagerId int,

    ManagerName nvarchar(50)) -- second result set definition starts here
);

Actual resultset return can be different from the resultset defined in RESULT SET clause in one of the following ways. number of result sets, number of columns, column name, nullability, and data type. If the number of result sets differs, an error occurs and the batch is aborted.

Using multiple parameters

Exceute statement allows to pass multiple parameters with variable as well hardcode values. Earlier it was accepting either variables or hardcode values.
Procedure accepts two parameters, OrderID (100 as hard coded value) and @OrderDate as variable as datetime.

  DECLARE @OrderDate datetime;
  SET @OrderDate = GETDATE();
  EXEC
dbo. GetOrderDetails, 100, @OrderDate;

Using EXECUTE AS USER

EXECUTE statement can be used to execute as user to switch context to another user.
Following example execute SQL statement to create table in context of XYZ user. XYZ user must be present in the datebase and needs permission to create table otherwise statement will get fail.

EXECUTE ('CREATE TABLE [dbo].[TestTable] (Id int, Name varchar(50))')
      AS USER = 'XYZ';

Tags: ,

SQL Server Denali

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud