Untitled Document
 
Untitled Document
NEWS
Store Procedure in SQL Server
Source : Store Procedure in SQL Server
Post date : 21-08-2013

Store Procedure in SQL Server

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

PAIDCIDComponentDescTotalQuantityStandardCostListPriceBOMLevelRL
827922Road Tire Tube11.49233.9921
518530Seat Post10021
835325Decal 120021

 

*/

 

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

----------

Untitled Document
 ARTIKEL / BERITA TERBARU
 PROJECT SEDANG BERJALAN
 Prima Supplier -AutoFarrel Tiban & Sei Panas
 Prima Laundry -Executive laundry sukajadi
 Prima Clinic -Klinik Dunia Medika, T.Uncang & Raja Medika, Batu Aji
 Prima Payroll -PT. Samchin Enginering, PT. Karya Sumber Daya, PT. Asiatec Recyclindo
 Prima Apotek -Apotek Penuin
 Prima POS -Keperasi Bosowa
Locations of visitors to this page

   
 
Untitled Document
YM SUPPORT     CS 1 :


Free counters!

copyright © 2013 PRIMA INFODATA , all right reserved | best view @ mozilla firefox | [ webmail ]