Saklı yordamda parametreleri belirtme

Şunlar için geçerlidir:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalitik Platform Sistemi (PDW)Microsoft Fabric'te SQL veritabanı

Yordam parametrelerini belirterek, çağıran programlar değerleri yordamın gövdesine geçirebilir. Bu değerler yordam yürütme sırasında çeşitli amaçlarla kullanılabilir. Parametre parametre OUTPUT olarak işaretlenmişse, yordam parametreleri de çağıran programa değer döndürebilir.

Bir yordamda en fazla 2.100 parametre olabilir; her birine bir ad, veri türü ve yön atanır. İsteğe bağlı olarak, parametrelere varsayılan değerler atanabilir.

Aşağıdaki bölümde, değerleri parametrelere geçirme ve bir yordam çağrısı sırasında parametre özniteliklerinin her birinin nasıl kullanıldığı hakkında bilgi sağlanır.

Note

Bu makalenin alıştırmaları için AdventureWorks örnek veritabanı serisine bakın. Daha fazla bilgi için bkz. AdventureWorks örnek veritabanları.

Değerleri parametrelere geçirme

Yordam çağrısıyla sağlanan parametre değerleri sabitler veya değişken olmalıdır; bir işlev adı parametre değeri olarak kullanılamaz. Değişkenler kullanıcı tanımlı veya @@spidgibi sistem değişkenleri olabilir.

Aşağıdaki örneklerde, uspGetWhereUsedProductIDyordamına parametre değerlerini geçirme işlemi gösterilmektedir. Parametrelerin sabitler ve değişkenler olarak nasıl geçirilebileceğini ve bir işlevin değerini iletmek için bir değişkenin nasıl kullanılabileceğini gösterirler.

USE AdventureWorks2022;  
GO  
-- Passing values as constants.  
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';  
GO  
-- Passing values as variables.  
DECLARE @ProductID int, @CheckDate datetime;  
SET @ProductID = 819;  
SET @CheckDate = '20050225';  
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;  
GO

Aşağıdaki örnek, bir işlev parametre değeri olarak geçirilemediğinden bir hata döndürür.

-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();  

Bunun yerine, aşağıdaki örnekte olduğu gibi parametreye bir işlev değeri geçirmek için bir değişken kullanın:

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO  

Parametre adlarını belirtme

Bir yordam oluşturulurken ve parametre adı bildirildiğinde, parametre adı tek bir @ karakteriyle başlamalı ve yordamın kapsamında benzersiz olmalıdır.

Parametreleri açıkça adlandırma ve yordam çağrısında her parametreye uygun değerleri atama, parametrelerin herhangi bir sırada sağlanmasını sağlar. Örneğin, yordam my_proc@first, @secondve @thirdadlı üç parametre bekliyorsa, yordama geçirilen değerler parametre adlarına atanabilir; örneğin: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Note

@parameter = valuebiçiminde bir parametre değeri sağlanırsa, sonraki tüm parametreler bu şekilde sağlanmalıdır. Parametre değerleri biçiminde @parameter = valuegeçirilmediyse, parametreler deyiminde listelendiği CREATE PROCEDURE için değerlerin aynı sırada (soldan sağa) sağlanması gerekir. Hem üstün okunabilirlik hem de saklı yordamın gelecekteki sürümleriyle uyumluluk için parametre adlarını belirtmek iyi bir uygulamadır.

Warning

Form @parameter = value şeklinde geçirilen herhangi bir parametrenin yanlış yazılması, SQL Server'ın hata oluşturmasına ve yordamın yürütülmesini engellemesine neden olur.

Parametre veri türlerini belirtme

Parametreler bir deyimde CREATE PROCEDURE bildirildiğinde bir veri türüyle tanımlanmalıdır. Bir parametrenin veri türü, yordam çağrıldığında parametre için kabul edilen değer türünü ve aralığını belirler. Örneğin, tinyint veri türüne sahip bir parametre tanımlarsanız, bu parametreye geçirildiğinde yalnızca 0 ile 255 arasında sayısal değerler kabul edilir. Bir yordam veri türüyle uyumsuz bir değerle yürütülürse hata döndürülür.

Parametre varsayılan değerlerini belirtme

Parametre bildirildiğinde belirtilen varsayılan değere sahipse parametre isteğe bağlı olarak kabul edilir. Yordam çağrısında isteğe bağlı bir parametre için değer sağlamak gerekli değildir.

Aşağıdaki durumlarda parametrenin varsayılan değeri kullanılır:

• Prosedür çağrısında parametre için bir değer belirtilmedi.
• DEFAULT anahtar sözcüğü, yordam çağrısında değer olarak belirtilir.

Note

Varsayılan değer, eklenmiş boşluklar veya noktalama işaretleri içeren bir karakter dizesiyse veya bir sayıyla başlıyorsa (örneğin, 6abc), tek, düz tırnak içine alınmalıdır.

Note

Azure Synapse Analytics veya Analytics Platform Sistemi'nde (PDW) varsayılan parametreler desteklenmez.

Parametre için varsayılan olarak uygun şekilde hiçbir değer belirtilemiyorsa, varsayılan olarak NULL belirtin. Yordam parametresi için bir değer olmadan yürütülürse, yordamın özelleştirilmiş bir ileti döndürmesini sağlamak iyi bir fikirdir.

Aşağıdaki örnek, uspGetSalesYTDtek bir giriş parametresiyle @SalesPerson yordamını oluşturur. NULL parametresi için varsayılan değer olarak atanır ve hata işleme deyimlerinde, yordamın @SalesPerson parametresi için bir değer olmadan yürütüleceği durumlar için özel bir hata iletisi döndürmek için kullanılır.

USE AdventureWorks2022;  
GO  
IF OBJECT_ID('Sales.uspGetSalesYTD', 'P') IS NOT NULL  
    DROP PROCEDURE Sales.uspGetSalesYTD;  
GO  
CREATE PROCEDURE Sales.uspGetSalesYTD  
    @SalesPerson nvarchar(50) = NULL  -- NULL default value  
AS   
    SET NOCOUNT ON;   

-- Validate the @SalesPerson parameter.  
IF @SalesPerson IS NULL  
BEGIN  
   PRINT 'ERROR: You must specify the last name of the sales person.'  
   RETURN  
END  
-- Get the sales for the specified sales person and   
-- assign it to the output parameter.  
SELECT SalesYTD  
FROM Sales.SalesPerson AS sp  
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID  
WHERE LastName = @SalesPerson;  
RETURN  
GO  

Aşağıdaki örnek prosedürü yürütür. İlk ifade, giriş değeri belirtmeden prosedürü yürütür. Bu, yordamdaki hata işleme deyimlerinin özel hata iletisini döndürmesine neden olur. İkinci deyim bir giriş değeri sağlar ve beklenen sonuç kümesini döndürür.

-- Run the procedure without specifying an input value.  
EXEC Sales.uspGetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.uspGetSalesYTD N'Blythe';  
GO  

Varsayılan değerleri olan parametreler isteğe bağlı olarak atlanabilirken, null değer kabul etmeyen parametrelerin listesi yalnızca kısaltılabilir. Örneğin, bir yordam beş parametreye sahipse ve @parameter = valueile parametre adları belirtilmemişse, dördüncü ve beşinci parametreler atlanabilir. Ancak, parametreler @parameter = valuebiçiminde sağlanmadığı sürece, beşinci parametre dahil edildiği sürece dördüncü parametre atlanamaz.

Varsayılan değerlerle birden çok parametre belirtme

Parametre adlarını belirtirseniz parametreleri atlayabilirsiniz. NULL varsayılan değerli birden fazla isteğe bağlı parametre içeren aşağıdaki saklı yordamı göz önünde bulundurun.

USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'Production.uspSearchList', 'P' ) IS NOT NULL   
    DROP PROCEDURE Production.uspSearchList;  
GO  
CREATE PROCEDURE Production.uspSearchList
      @ListPrice money 
    , @ProductCategoryID int       = NULL  -- NULL default value  
    , @ProductSubcategoryID int    = NULL  -- NULL default value  
    , @ProductBusinessEntityID int = NULL  -- NULL default value  
AS  
    SET NOCOUNT ON;  
    SELECT 
        p.Name, p.Class, p.ListPrice, p.ProductID, pc.Name, psc.Name, v.Name
    FROM 
        Production.Product AS p
    INNER JOIN Production.ProductSubCategory AS psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS pc ON psc.ProductCategoryID = pc.ProductCategoryID
    INNER JOIN Purchasing.ProductVendor AS pv ON p.ProductID = pv.ProductID
    INNER JOIN Purchasing.Vendor AS v ON pv.BusinessEntityID = v.BusinessEntityID
    WHERE (p.ListPrice < @ListPrice)
    AND   (pc.ProductCategoryID = @ProductCategoryID or @ProductCategoryID IS NULL)
    AND   (psc.ProductSubcategoryID = @ProductSubcategoryID or @ProductSubcategoryID IS NULL)
    AND   (pv.BusinessEntityID = @ProductBusinessEntityID or @ProductBusinessEntityID IS NULL);
GO

Aşağıdaki örneklerden oluşan serinin gösterdiği gibi, her biri @parameter = valuebiçiminde parametre adıyla sağlandığından, varsayılan değerlerle parametreleri belirtebilir veya atlayabilirsiniz:

--Find all Products with a list price less than 150.00 and in the ProductCategoryID = 4
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36
EXEC Production.uspSearchList @ListPrice = 150, @ProductSubCategoryID = 36;
--Find all Products with a list price less than 150.00 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductBusinessEntityID = 1498;
--Find all Products with a list price less than 150.00 and in the ProductSubCategoryID = 36 and from @ProductBusinessEntityID = 1498
EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4, @ProductBusinessEntityID = 1498;

Aşağıdaki örnek geçerli bir T-SQL söz dizimi değildir, çünkü bir parametre adı sağlandıktan sonra sonraki tüm parametreler aynı şekilde sağlanmalıdır. Tüm değerler için parametre adları sağlamak her zaman önerilir ve hataları ve karışıklığı önler.

EXEC Production.uspSearchList @ListPrice = 150, 4, 1498;

Parametre yönünü belirtin

Bir parametrenin yönü ya girdidir, bu durumda bir değer prosedürün gövdesine iletilir, ya da çıktıdır, bu durumda prosedür çağıran programa bir değer döndürür. Varsayılan değer bir giriş parametresidir.

Bir çıkış parametresi belirtmek için anahtar sözcüğü deyimindeki OUTPUT parametrenin tanımında CREATE PROCEDURE belirtilmelidir. Yordam, çıkış parametresinin geçerli değerini, yordam çıktığında çağıran programa döndürür. Çağıran program, parametrenin OUTPUT değerini çağıran programda kullanılabilecek bir değişkene kaydetmek için yordamı yürütürken anahtar sözcüğünü de kullanmalıdır.

Aşağıdaki örnek, belirtilen tutarı aşmayan fiyatları olan ürünlerin listesini döndüren Production.usp_GetList yordamını oluşturur. Örnekte birden çok deyimin ve birden çok SELECTOUTPUT parametrenin kullanılması gösterilmektedir. OUTPUT parametreleri bir dış yordama, toplu işleme veya birden çok Transact-SQL deyiminin yordam yürütme sırasında ayarlanan bir değere erişmesine olanak sağlar.

USE AdventureWorks2022;  
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  

700 ABD dolarından daha düşük olan Adventure Works ürünlerinin (usp_GetList) listesini döndürmek için Bikes yürütür. ve parametreleri, OUTPUT@cost@compareprices penceresinde bir ileti döndürmek için akış denetimi diliyle birlikte kullanılır.

Note

Değişken, OUTPUT yordam oluşturma sırasında ve değişkenin kullanımı sırasında tanımlanmalıdır. Parametre adı ve değişken adının eşleşmesi gerekmez. Ancak, veri türü ve parametre konumlandırması eşleşmelidir (@listprice = variable kullanılmadığı sürece).

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 sonuç kümesi 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.  

İlgili içerik

• PROSEDÜR OLUŞTUR (Transact-SQL)
• Parameters
• ÇALIŞTIR (Transact-SQL)