DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to SELECT from a stored procedure?

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)

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

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

CREATE PROC 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 uspGetBOM 900, '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

                        WHEN DATEPART(QQ,OrderDate) = 1 THEN SubTotal

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

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 2 THEN SubTotal

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

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 3 THEN SubTotal

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

       COALESCE(CONVERT(VARCHAR,SUM(CASE

                        WHEN DATEPART(QQ, OrderDate) = 4 THEN SubTotal

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

FROM  Sales.SalesOrderHeader soh

GROUP BY YEAR(OrderDate) ORDER BY 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 * FROM OPENQUERY ([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

FROM OPENQUERY(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 is NULL

 

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

DROP TABLE #Buffer

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

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


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

CREATE TABLE #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'

SELECT TOP(3) * FROM #BillOfMaterials ORDER BY NEWID()

/* Results

PAID CID ComponentDesc TotalQuantity StandardCost ListPrice BOMLevel RL
827 922 Road Tire Tube 1 1.4923 3.99 2 1
518 530 Seat Post 1 0 0 2 1
835 325 Decal 1 2 0 0 2 1

 

*/

 

DROP TABLE #BillOfMaterials

GO

-- SQL select into from stored procedure execution

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

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

SELECT RowsReturned=COUNT(*) FROM #BOM

-- 87

DROP TABLE #BOM
GO

The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!

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

-- 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

CREATE PROCEDURE 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 AS State,

             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 IS NULL)

             AND (SP.Name = @State

                   OR @State IS NULL)

             AND (CR.Name = @Country

                   OR @Country IS NULL)

    ORDER BY 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 * FROM OPENQUERY (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 * FROM OPENQUERY (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 * FROM OPENQUERY (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 * FROM OPENQUERY (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 * FROM OPENQUERY (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

ORDER BY Newid()

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

*/

DROP TABLE #BOM800

GO

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

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

-- SQL finding stored procedure result columns meta data

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

SELECT TOP 0 *

INTO tempdb.dbo.BOM

FROM OPENQUERY ([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

CREATE TABLE [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

CREATE PROCEDURE 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

        INNER JOIN Sales.SalesOrderHeader soh

        ON sp.SalesPersonID = soh.SalesPersonID

        INNER JOIN Sales.SalesTerritory st

        ON sp.TerritoryID = st.TerritoryID

        INNER JOIN HumanResources.Employee e

        ON soh.SalesPersonID = e.EmployeeID

        INNER JOIN Person.Contact c

        ON e.ContactID = c.ContactID

    ) AS soh

PIVOT

(

    SUM(SubTotal)

    FOR FiscalYear

    IN ( [2003], [2004])

) AS pvt

ORDER BY SalesStaff;

 

GO

 

-- Create a temporary or permanent table for results

-- Table structure must match sproc results structure

CREATE TABLE #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 ORDER BY 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

DROP TABLE #SalesByYear

GO

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

The BEST 70-461 SQL Server 2012 Querying Exam Prep Book!

USE AdventureWorks;

 

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

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

'SET FMTONLY OFF EXEC sp_who');

GO

 

-- Query results can be stored in a temporary table

SELECT * INTO #spwho

FROM OPENROWSET ( '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 * FROM OPENROWSET ( '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

INNER JOIN OPENROWSET ( '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'

CREATE TABLE #ERRORLOG (

            LogDate datetime,

            ProcessInfo varchar(64),

            [Text] NVARCHAR(max))

 

-- Populate table from sproc - insert exec stored procedure

INSERT #ERRORLOG

EXEC sp_executesql @Command

 

-- Select top 10 results only

SELECT TOP(10) * FROM #ERRORLOG ORDER BY 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.

*/

DROP TABLE #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

CREATE PROCEDURE SelectFromDynamicSrpoc

AS

  BEGIN

    DECLARE  @SQL NVARCHAR(MAX)

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

                AdventureWorks.Production.Product

                ORDER BY NEWID()'

    -- Dynamic SQL

    EXEC Sp_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

DROP PROCEDURE SelectFromDynamicSrpoc

DROP TABLE #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''')

*/
EXEC sp_executeSQL @DynamicSQL

 

SELECT   TOP ( 5 ) *

FROM    PartsUsage

ORDER BY NEWID()

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

DROP TABLE PartsUsage

GO

----------

Related articles:

OPENQUERY (Transact-SQL)

How to Share Data Between Stored Procedures

SQL Stored Procedures

http://www.sqlusa.com/bestpractices2008/stored-procedure-parameters/

SQL Server DBA Checklist

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE