Aracılığıyla paylaş

  • Makale

Saklı yordamlar (veritabanı altyapısı) oluşturma

Saklı yordamlar oluşturma yordamı kullanarak oluşturduğunuz Transact-SQL deyim.

Bir saklı yordam oluşturmadan önce göz önünde bulundurun:

  • create procedure deyimi, tek bir toplu iş diğer sql deyimleri ile birleştirilemez.

  • Yordamları oluşturmak için create procedure veritabanında ve alter izni yordamı oluşturulmaktadır şema üzerinde olması gerekir.clr Saklı yordamlar için ya da başvurulan derleme sahibi olduğunuz gerekir <method_specifier>, o derleme. başvurular izninizin olması veya

  • Saklı yordamlar şema kapsamlı nesnelerdir ve adlarını tanımlayıcıları kuralları izlemeniz gerekir.

  • Yalnızca geçerli veritabanında saklı yordam oluşturabilirsiniz.

Bir saklı yordam oluştururken belirtmeniz gerekir:

  • Herhangi parametrelerini ve arama yordamı veya toplu iş için çıkış parametreleri girin.

  • Diğer yordamları çağıran dahil olmak üzere veritabanında işlemleri gerçekleştirmek programlama deyimleri.

  • Arama yordamı veya toplu iş iş başarı veya başarısızlık (ve başarısızlık nedeni) belirtmek için döndürülen durum değeri.

  • Tüm hata işleme yakalamak ve olası hataları işlemek için gereken deyimleri.

    Teslim etme ERROR_LINE ve error_procedure gibi işlevleri hata saklı yordam içinde belirtilebilir.Daha fazla bilgi için bkz: kullanarak deneyin...Transact-SQL'de catch.

Saklı yordamlar adlandırma

We recommend that you do not create any stored procedures using sp_ as a prefix.SQL Server uses the sp_ prefix to designate system stored procedures.Seçtiğiniz ad, bazı gelecekteki sistem yordamı ile çakışabilir.Çünkü sistem yordamı için adı bağlar uygulamanızın nonschema tam adı başvurularını kullanır ve kendi yordam adı sistem yordamı adı ile çakışıyor, uygulamanızın çalışmamasına neden olur kendi.

Kullanıcı tanımlı bir saklı yordam sistem saklı yordamı ile ya da olduğu gibi aynı ada sahip nonqualified ya dbo şema asla yürütülür; sistem saklı yordamı her zaman bunun yerine çalıştırır.Bu davranış aşağıdaki örnekte gösterilmiştir.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Person;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

Açık şeması niteleyici kullanılarak hafif performans avantajı sağlar.Ad çözümlemesi, biraz daha hızlı, Veritabanı Altyapısı sahip değil arama birden çok şema bulma yordamı.Daha fazla bilgi için bkz: saklı yordamı yürütme.

Geçici saklı yordamlar

Özel ve genel geçici saklı yordamlar, geçici tablolara benzer # ve yordam adı eklendi ## prefixes ile oluşturulabilir.# yerel geçici olarak saklanan yordam gösterir; ## Genel geçici olarak saklanan yordam gösterir.Bu yordamları sonra mevcut SQL Server olan kapatmak kapalı.

Geçici saklı yordamlar yararlı olan önceki sürümleri için bağlanırken SQL Server yeniden yürütme planları için kullanımını desteklemeyen Transact-SQL ifadeleri veya toplu işlemleri.Bağlanma uygulamaları SQL Server 2000 ve daha yüksek kullanması gereken sp_executesql sistem saklı yordamı yerine, geçici saklı yordamlar.Yerel geçici bir yordam oluşturulan bağlantı olabilir yürütmek ve yordamı otomatik olarak silinir bağlantı kapalı olduğunda.

Herhangi bir bağlantısı olabilir yürütmek genel geçici olarak saklanan yordam.Genel geçici olarak saklanan yordam yordam oluşturan kullanıcı tarafından kullanılan bağlantı kapatılır ve yordam diğer bağlantılar tarafından şu anda yürütülen herhangi bir sürümü tamamlandı kadar bulunmaktadır.Başka hiçbir yürütme genel geçici olarak saklanan yordam yordam oluşturmak için kullanılan bağlantı kapatıldıktan sonra izin verilir.Saklı yordam yürütme zaten başlamış bağlantıları tamamlamak için izin verilir.

# İle değil öneki bir saklı yordam, veya ## doğrudan oluşturulan tempdb veritabanı saklı yordamdır otomatik olarak ne saat silinmiş SQL Server kapatıldıktan kapalı çünkü tempdb her saat yeniden SQL Server başlatılır.Doğrudan oluşturulan yordamlar tempdb bile oluşturma bağlantı sonlandırıldıktan sonra mevcut.

Not

Geçici saklı yordamlar yoğun kullanımını oluşturup çekişmesi üzerinde sistem tabloları , tempdb ve performansı olumsuz etkileyebilir.It is recommended that sp_executesql be used instead.sp_executesql does not store data in the system tables and therefore avoids the problem.

clr Saklı yordamlar gibi geçici saklı yordamlar oluşturulamaz.

Örnekler

A.Karmaşık bir select ile basit bir yordamı kullanma

Tüm çalışanların aşağıdaki saklı yordam döndürür (sağlanan ilk ve son adları), kendi başlıkları ve bölüm adlarının bir görünümde.Bu saklı yordam parametreleri kullanın.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

The uspGetEmployees stored procedure can be executed in these ways:

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B.Parametreleriyle basit bir yordamı kullanma

Aşağıdaki saklı yordam yalnızca belirtilen personel döndürür (sağlanan ilk ve son adı), kendi başlık ve her departman adı bir görünümde.Bu saklı yordam tam eşleşmeleri geçirilen parametreleri kabul eder.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

    SET NOCOUNT ON;
    SELECT FirstName, LastName,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

The uspGetEmployees stored procedure can be executed in the following ways:

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C.Joker karakter parametreleriyle basit bir yordamı kullanma

Aşağıdaki saklı yordam yalnızca belirtilen çalışanlar döndürür (sağlanan ilk ve son adları), kendi başlıkları ve bunların bölümlerden bir görünüm.Bu saklı yordam desen geçirilen parametrelerle eşleşen veya sağlanmayan, önceden ayarlanmış varsayılan kullanır (son d harfi ile başlayan bir adları).

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

The uspGetEmployees2 stored procedure can be executed in many combinations.Yalnızca birkaç birleşimleri burada gösterilir:

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D.Çıkış parametreleri kullanma

Aşağıdaki örnek oluşturur uspGetList saklı yordam, belirtilen bir tutarı aşan fiyatları sahip ürünlerin listesini döndürür.Bu örnek, birden çok select deyimleri ve birden çok çıkış parametresi kullanarak gösterir.Harici bir yordam, bir toplu iş veya birden çok çıkış parametresi izin Transact-SQL deyim erişim değeri küme sırasında yordamı yürütme.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Yürütme uspGetList bir listesini döndürmek için Adventure Works 700 $ daha az maliyet ürünleri (bisiklet).Çıkış parametreleri @ Maliyet ve @ compareprices bir ileti dönmek için akış denetimli dil ile kullanılan iletileri pencere.

Not

Yordam oluşturma sırasında olduğu gibi değişken kullanımı sırasında çıkış değişkeni tanımlanmalıdır.The parameter name and variable name do not have to match; however, the data type and parameter positioning must match (unless @ListPrice= variable is used).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Kısmi bir sonuç küme aşağıdadır:

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.