[SQL Server] Comment récupérer le résultat d'une procédure stockée via SELECT from ?

 

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*FROMOPENQUERY ([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

  EXECsp_executeSQL@SQL

END

GO

EXEC uspGetBOM900,'2004-03-15'

GO

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

DROPTABLE BOM

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

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

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

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

USE AdventureWorks2008;

GO

CREATEPROCEDURE sprocSalesByQuarter

AS

BEGIN -- sproc definition

SETNOCOUNTON-- turn off rows affected messages

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

GROUPBYYEAR(OrderDate)ORDERBYYEAR(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

EXECmaster.dbo.sp_addlinkedserver@server =N'localhost',

                                   @srvproduct=N'SQL Server'

SELECT*INTO tempSpwho

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

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

CREATETABLE #Buffer( Line VARCHAR(256))

-- SQL insert into execute - sql execute string

INSERTINTO #Buffer

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

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

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

 

*/

 

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

----------

 

 
 
 

Commentaires   

0 #3 Hannah 29-10-2017 13:54
I have noticed you don't monetize your blog, don't waste your traffic,
you can earn extra bucks every month because you've got high quality content.

If you want to know how to make extra bucks, search for: Mrdalekjd methods for $$$

Feel free to visit my page FirstStefanie: https://10Heath.blogspot.se
Citer
0 #2 Normand 26-10-2017 14:50
I think your blog needs some fresh content. Writing manually takes a lot of time, but there is
tool for this time consuming task, search for; Ssundee advices unlimited content for
any blog

Here is my blog ... Avery04: https://Launa04.blogspot.com
Citer
0 #1 Millie 10-10-2017 16:22
Great delivery. Great arguments. Keep up the great spirit.


My homepage: https://www.viagrapascherfr.com/acheter-sildenafil-pfizer-tapasztalatok/: https://www.viagrapascherfr.com/acheter-sildenafil-pfizer-tapasztalatok/
Citer

Ajouter un Commentaire


Code de sécurité
Rafraîchir