ZOUHAIER KHARROUBI

Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate how to make system and user stored procedure EXEC (EXECUTE) to generate table-like results or SELECT INTO tables:

-- SQL select from stored procedure - openquery stored procedure
-- For local server the DATA ACCESS server option must be turned on (see below)

-- exec sp_serveroption 'localhost', 'data access', 'true'

SELECT * FROM OPENQUERY ([DELLSTAR\SQL2008],

'EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]   800, ''2004-01-01''')

/* Partial results

ProductAssemblyID ComponentID       ComponentDesc

800               518               ML Road Seat Assembly

800               806               ML Headset

800               812               ML Road Handlebars

*/
------------

-- OPENQUERY usage within stored procedure - dynamic SQL execution

------------

CREATEPROC uspGetBOM @ProductID int, @Date date

AS

BEGIN

  DECLARE @SQL nvarchar(max)=

  'SELECT *

  INTO   BOM

  FROM   OPENQUERY(localhost,'' EXECUTE

         [AdventureWorks].[dbo].[uspGetBillOfMaterials] '+

                           convert(varchar,@ProductID)+

                           ','''''+convert(varchar,@Date)+''''''')'

  PRINT @SQL

  EXEC sp_executeSQL @SQL

END

GO

EXEC uspGetBOM900,'2004-03-15'

GO

SELECT * FROM BOM     -- Permanent or global temporary table scoped beyond the sproc

DROP TABLE BOM

------------

------------

-- SQL stored procedure basic syntax - creating t sql stored procedures

------------

USE AdventureWorks2008;

GO

CREATE PROCEDURE sprocSalesByQuarter

AS

BEGIN -- sproc definition

SET NOCOUNT ON-- turn off rows affected messages

SELECT YEAR=YEAR(OrderDate),

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHENDATEPART(QQ,OrderDate)= 1 THEN SubTotal

                                    END),1),'')AS'Q1',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHENDATEPART(QQ, OrderDate)= 2 THEN SubTotal

                                    END),1),'')AS'Q2',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHENDATEPART(QQ, OrderDate)= 3 THEN SubTotal

                                    END),1),'')AS'Q3',

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHENDATEPART(QQ, OrderDate)= 4 THEN SubTotal

                                    END),1),'')AS'Q4'

FROM  Sales.SalesOrderHeader soh

GROUPBY YEAR(OrderDate)ORDERBY YEAR(OrderDate)

END-- sproc definition

GO

-- SQL test stored procedure with no parameters - sql execute stored procedure

-- SQL Server select from stored procedure results

SELECT * FROMOPENQUERY ([DELLSTAR\SQL2008],

'EXECUTE [AdventureWorks2008].[dbo].sprocSalesByQuarter')

GO

/*

YEAR  Q1                Q2                Q3                Q4

2001                                      5,294,961.92      7,671,148.64

2002  6,678,449.12      7,430,122.29      12,179,372.04     9,798,486.39

2003  7,738,309.35      9,727,845.55      16,488,806.73     15,192,201.07

2004  12,824,418.47     16,262,217.91     50,840.63  

*/

------------

-- Using localhost with OPENQUERY - One time only: add as linked server

EXEC master.dbo.sp_addlinkedserver @server =N'localhost',

                                   @srvproduct=N'SQL Server'

SELECT * INTO tempSpwho

FROM OPENQUERY(localhost,'exec sp_who')

SELECT * FROM tempSpwho

------------

-- WARNING: The OPENQUERY/OPENROWSET method has some restrictions

SELECT*INTO SPWHO2

FROMOPENQUERY(YOURSERVER,'exec sp_who2')

/* Msg 7357, Level 16, State 2, Line 1

Cannot process the object "exec sp_who2". The OLE DB provider "SQLNCLI10"

for linked server "YOURSERVER" indicates that either the object has no columns

or the current user does not have permissions on that object. */

------------

------------

-- Transferring multiple result sets into a table

------------

-- Transfer stored procedure results into a flat file

-- SQLCMD export query results into text file

EXECUTE MASTER.dbo.xp_cmdshell

'SQLCMD -SDELLSTAR\SQL2008 -E -Q"execute AdventureWorks2008.dbo.sp_spaceused"
-o"C:\data\export\spaceused.txt" -s"" '

, no_output

-- Upload content of flat file into temp table using free format

CREATE TABLE #Buffer( Line VARCHAR(256))

-- SQL insert into execute - sql execute string

INSERT INTO #Buffer

EXECUTE MASTER.dbo.xp_cmdshell'type "C:\data\export\spaceused.txt"'

DELETE FROM #Buffer WHERE Line isNULL

SELECT Line FROM   #Buffer

/*

Line

database_name              database_size     unallocated space

-------------------------------------------------------------------

AdventureWorks2008          213.50 MB         4.22 MB          

reserved          data              index_size        unused           

-------------------------------------------------------------------

195872 KB         106616 KB         82592 KB          6664 KB          

*/

-- Cleanup

DROPTABLE #Buffer

EXECUTEMASTER.dbo.xp_cmdshell'DEL "C:\data\export\spaceused.txt"', no_output

------------

-- SQL insert exec for data sharing between stored procedures - SELECT from results table

CREATETABLE #BillOfMaterials(

  ProductAssemblyID INT,

  ComponentID       INT,

  ComponentDesc     NVARCHAR(50),

  TotalQuantity     NUMERIC(38,2),

  StandardCost      MONEY,

  ListPrice         MONEY,

  BOMLevel          SMALLINT,

  RecursionLevel    INT);

INSERT #BillOfMaterials

EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]   800,'2004-01-01'

SELECTTOP(3)*FROM #BillOfMaterials ORDERBYNEWID()

/* Results

*/

DROPTABLE #BillOfMaterials

GO

-- SQL select into from stored procedure execution

SELECT*INTO #BOM FROMOPENQUERY ([DELLSTAR\SQL2008],

'EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials] 800, ''2004-01-01''')

SELECT RowsReturned=COUNT(*)FROM #BOM

-- 87

DROPTABLE #BOM
GO

------------

-- Stored procedure with optional parameter list - SELECT from OPENQUERY

------------

-- MSSQL Server select from stored procedure results

USE AdventureWorks;

GO

-- SQL Server stored procedure create - T-SQL Select from sproc

CREATEPROCEDURE sprocVendorListByGeograpy

                @City    VARCHAR(30)  =NULL,

                @State   VARCHAR(30)  =NULL,

                @Country VARCHAR(50)  =NULL

AS

  BEGIN

    SELECT   V.VendorID,

             V.Name  AS Vendor,

             A.City,

             SP.Name ASState,

             CR.Name AS Country

    FROM     Purchasing.Vendor AS V

             JOIN Purchasing.VendorAddress AS VA

               ON VA.VendorID = V.VendorID

             JOIN Person.Address AS A

               ON A.AddressID = VA.AddressID

             JOIN Person.StateProvince AS SP

               ON SP.StateProvinceID = A.StateProvinceID

             JOIN Person.CountryRegion AS CR

               ON CR.CountryRegionCode = SP.CountryRegionCode

-- Stored procedure optional parameter filtering

-- Dynamic SQL can be avoided in some cases with WHERE filtering like below

    WHERE    (A.City = @City

               OR @City ISNULL)

             AND(SP.Name = @State

                   OR @State ISNULL)

             AND(CR.Name = @Country

                   OR @Country ISNULL)

    ORDERBY Country,

             State,

             City,

             Vendor

  END

GO

-- Execute stored procedure by supplying all parameters

-- For openquery, the single quotes must be doubled up

-- Select from stored procedure

EXEC sprocVendorListByGeograpy 'San Francisco','California','United States'

SELECT*FROMOPENQUERY(DELLSTAR,

'EXEC AdventureWorks.dbo.sprocVendorListByGeograpy ''San Francisco'',

''California'',''United States''')

/*

VendorID    Vendor            City              State       Country

42          Legend Cycles     San Francisco     California  United States

*/

-- Execute stored procedure

-- Full list - no filtering

-- SQL Server select from sproc

EXEC sprocVendorListByGeograpy

SELECT*FROMOPENQUERY(DELLSTAR,

'EXEC AdventureWorks.dbo.sprocVendorListByGeograpy')

-- (104 row(s) affected)

/* Partial results

VendorID    Vendor                        City        State       Country

88          Greenwood Athletic Company    Lemon Grove Arizona     United States

100         Holiday Skate & Cycle         Lemon Grove Arizona     United States

97          Northwind Traders             Phoenix     Arizona     United States

38          Allenson Cycles               Altadena    California  United States

48          Gardner Touring Cycles        Altadena    California  United States

*/

-- Execute stored procedure by supplying the City parameter

EXEC sprocVendorListByGeograpy 'Berkeley'

EXEC sprocVendorListByGeograpy Berkeley

SELECT*FROMOPENQUERY(DELLSTAR,

'EXEC AdventureWorks.dbo.sprocVendorListByGeograpy ''Berkeley''')

/*

VendorID    Vendor                  City        State       Country

76          Cruger Bike Company     Berkeley    California  United States

98          Trikes, Inc.            Berkeley    California  United States

*/

-- Execute stored procedure by supplying the State parameter

EXEC sprocVendorListByGeograpy NULL,'California'

SELECT*FROMOPENQUERY(DELLSTAR,

'EXEC AdventureWorks.dbo.sprocVendorListByGeograpy NULL,''California''')

-- (39 row(s) affected)

-- Execute stored procedure by supplying the Country parameter

EXEC sprocVendorListByGeograpy NULL,NULL,'United States'

SELECT*FROMOPENQUERY(DELLSTAR,

'EXEC AdventureWorks.dbo.sprocVendorListByGeograpy NULL,NULL,''United States''')

-- (104 row(s) affected) -- entire set i.e. all vendors are U.S. based

------------

-- Create temporary table with the output of stored procedure

-- Select into from stored procedure

-- Select from stored procedure

-- SRVOMEGA can be local server or linked server

SELECT*

INTO   #BOM800

FROM   OPENQUERY(SRVOMEGA,' EXECUTE [AdventureWorks].[dbo].[uspGetBillOfMaterials]

                            800   ,''2004-02-01''')

GO

SELECT   TOP( 5 )*

FROM     #BOM800

ORDERBYNewid()

GO

/* Partial results

ProductAssemblyID       ComponentID       ComponentDesc     TotalQuantity

329                     482               Metal Sheet 2     1.00

3                       2                 Bearing Ball      10.00

806                     323               Crown Race        1.00

532                     484               Metal Sheet 7     1.00

812                     398               Handlebar Tube    1.00

*/

DROPTABLE #BOM800

GO

------------  

------------

-- SQL finding stored procedure result columns meta data

------------

SELECTTOP 0 *

INTO tempdb.dbo.BOM

FROMOPENQUERY ([DELLSTAR\SQL2008],

'EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]    800  ,''2004-02-01''')

-- (0 row(s) affected) empty table created in the results format of the sproc

GO

-- Script out table using Object Explorer

-- This is the output column format of the stored procedure

CREATETABLE [dbo].[BOM](

      [ProductAssemblyID] [int] NULL,

      [ComponentID] [int] NULL,

      [ComponentDesc] [nvarchar](50)NULL,

      [TotalQuantity] [numeric](38, 2)NULL,

      [StandardCost] [money] NULL,

      [ListPrice] [money] NULL,

      [BOMLevel] [smallint] NULL,

      [RecursionLevel] [int] NULL

)ON [PRIMARY]

GO

-- INSERT - EXEC can be used due to the table matching the stored procedure

INSERT tempdb.dbo.BOM

EXECUTE [AdventureWorks2008].[dbo].[uspGetBillOfMaterials]    800  ,'2004-02-01'

-- (87 row(s) affected)

------------

------------

-- Stored procedure without parameters for reporting

------------

-- Select from stored procedure

-- Use INSERT...EXEC sproc statement to populate a (temporary) table

-- SQL create stored procedure

USE AdventureWorks;

GO

CREATEPROCEDURE sprocSalesByYear AS

SELECT

    SalesStaff,

    SalesTerritory,

    [YY2003]='$'+CONVERT(varchar,[2003],1),

    [YY2004]='$'+CONVERT(varchar,[2004],1)

FROM (SELECT

        soh.SalesPersonID

        ,c.FirstName +' '+COALESCE(c.MiddleName,'')+' '+ c.LastName AS SalesStaff

        ,e.Title

        ,st.Name AS SalesTerritory

        ,soh.SubTotal

        ,YEAR(DATEADD(m, 6, soh.OrderDate))AS FiscalYear

    FROM Sales.SalesPerson sp

        INNERJOIN Sales.SalesOrderHeader soh

        ON sp.SalesPersonID = soh.SalesPersonID

        INNERJOIN Sales.SalesTerritory st

        ON sp.TerritoryID = st.TerritoryID

        INNERJOIN HumanResources.Employee e

        ON soh.SalesPersonID = e.EmployeeID

        INNERJOIN Person.Contact c

        ON e.ContactID = c.ContactID

    )AS soh

PIVOT

(

    SUM(SubTotal)

    FOR FiscalYear

    IN( [2003], [2004])

)AS pvt

ORDERBY SalesStaff;

GO

-- Create a temporary or permanent table for results

-- Table structure must match sproc results structure

CREATETABLE #SalesByYear(

SalesStaff nvarchar(50),

Territory nvarchar(35),

[2003] varchar(32),

[2004] varchar(32)

)

GO

-- SQL insert exec

INSERT #SalesByYear

EXEC sprocSalesByYear

GO

-- Check and use results from temporary table

SELECT*FROM #SalesByYear ORDERBY SalesStaff

GO

/* Partial results

SalesStaff              Territory         2003              2004

David R Campbell        Northwest         $1,377,431.33     $1,930,885.56

Garrett R Vargas        Canada            $1,480,136.01     $1,764,938.99

Jae B Pak               United Kingdom    $5,287,044.31     $5,015,682.38

Jillian  Carson         Central           $4,991,867.71     $3,857,163.63

*/

-- Cleanup

DROPTABLE #SalesByYear

GO

------------  

USE AdventureWorks;

-- Getting table-like results from a system stored procedure

SELECT*FROMOPENROWSET('SQLOLEDB','SERVER=.;Trusted_Connection=yes',

'SET FMTONLY OFF EXEC sp_who');

GO

-- Query results can be stored in a temporary table

SELECT*INTO #spwho

FROMOPENROWSET('SQLOLEDB','SERVER=.;Trusted_Connection=yes',

'SET FMTONLY OFF EXEC sp_who');

GO

SELECT*FROM #spwho

GO

-- Getting table-like results from user stored procedure

SELECT*FROMOPENROWSET('SQLOLEDB','SERVER=.;Trusted_Connection=yes',

'SET FMTONLY OFF EXEC AdventureWorks.dbo.uspGetBillOfMaterials 800, ''2004-02-01''');

GO

-- Table-like transformation can be used in a JOIN

SELECT p.*FROM Production.Product p

INNERJOINOPENROWSET('SQLOLEDB','SERVER=.;Trusted_Connection=yes',

'SET FMTONLY OFF EXEC AdventureWorks.dbo.uspGetBillOfMaterials 800, ''2004-02-01''') bom

on p.ProductID=bom.ComponentID

GO

------------

-- Select from system stored procedure- xp_readerrorlog external procedure

-- Create temporary table method- SQL create temporary table

DECLARE @Command nvarchar(128)

SET @Command ='exec master.dbo.xp_readerrorlog'

CREATETABLE #ERRORLOG(

            LogDate datetime,

            ProcessInfo varchar(64),

            [Text] NVARCHAR(max))

-- Populate table from sproc- insert exec stored procedure

INSERT #ERRORLOG

EXECsp_executesql@Command

-- Select top 10 results only

SELECTTOP(10)*FROM #ERRORLOG ORDERBY LogDate

GO

/* Partial results

LogDate                 ProcessInfo       Text

2008-12-30 15:05:35.570 Server            Server process ID is 2180.

2008-12-30 15:05:35.570 Server            Authentication mode is MIXED.

*/

DROPTABLE #ERRORLOG

GO

------------

----------

-- SELECT from dynamic stored procedure execution INTO new table

----------

/* Data access must be turned on (1) for THIS local server (not linked server)

USE master

SELECT is_data_access_enabled  from sys.servers where name='SRVOMEGA'

EXEC sp_serveroption 'SRVOMEGA', 'data_access', 'on'

-- Alternate script

exec sp_serveroption @server = 'PRODSVR\SQL2008'

      ,@optname = 'DATA ACCESS'

      ,@optvalue = 'TRUE'

*/

----------

-- Dynamic stored procedure to temporary table

----------

USE tempdb;

GO

-- SQL create dynamic stored procedure

CREATEPROCEDURE SelectFromDynamicSrpoc

AS

  BEGIN

    DECLARE  @SQL NVARCHAR(MAX)

    SET @SQL ='SELECT top (3) * from                

                AdventureWorks.Production.Product

                ORDER BY NEWID()'

    -- Dynamic SQL

    EXECSp_executesql    @SQL

  END

GO

-- Test stored procedure

EXEC tempdb.dbo.SelectFromDynamicSrpoc

GO

-- Select into temporary table from OPENQUERY stored procedure execution

-- SQL select into temp table

SELECT*INTO   #prod

FROM   Openquery(SRVOMEGA,'exec tempdb.dbo.SelectFromDynamicSrpoc')

GO

SELECT ProductID, ProductName=Name, ListPrice

FROM   #prod

/* Results

ProductID   ProductName                   ListPrice

949         LL Crankset                   175.49

680         HL Road Frame - Black, 58     1431.50

358         HL Grip Tape                  0.00

*/

GO

-- Cleanup

DROPPROCEDURE SelectFromDynamicSrpoc

DROPTABLE #prod

GO

----------

----------

-- Find where ProductID=3 is being used and store results in table

----------

-- SQL select into table create from sproc

-- T-SQL dynamic SQL OPENQUERY - MSSQL QUOTENAME - SERVERNAME dynamic

DECLARE @DynamicSQL nvarchar(max)=

      'SELECT *

       INTO   PartsUsage

       FROM   OPENQUERY('+QUOTENAME(CONVERT(sysname,@@SERVERNAME))+',

                ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID]  3,

            ''''2003-12-01'''''')'

PRINT @DynamicSQL

/*

SELECT *

       INTO   PartsUsage

       FROM   OPENQUERY([PRODSVR\SQL2008],

                'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID]  3,

            ''2003-12-01''')

*/
EXECsp_executeSQL@DynamicSQL

SELECT   TOP ( 5 )*

FROM    PartsUsage

ORDERBYNEWID()

GO

/* Partial results

ProductAssemblyID ComponentID       ComponentDesc

769               994               Road-650 Black, 48

775               996               Mountain-100 Black, 38

787               995               Mountain-300 Black, 44

768               994               Road-650 Black, 44

757               995               Road-450 Red, 48

*/

-- Cleanup

DROPTABLE PartsUsage

GO

----------