-- 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
------------
-- 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
------------
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
----------
Source : sqlusa
