Bagikan melalui

Tentukan parameter dalam prosedur tersimpan

  • Artikel

Berlaku untuk: Database SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL database di Microsoft Fabric

Dengan menentukan parameter prosedur, program panggilan dapat meneruskan nilai ke dalam isi prosedur. Nilai-nilai tersebut dapat digunakan untuk berbagai tujuan selama eksekusi prosedur. Parameter prosedur juga dapat mengembalikan nilai ke program panggilan jika parameter ditandai sebagai parameter OUTPUT.

Prosedur dapat memiliki maksimum 2100 parameter; masing-masing diberi nama, jenis data, dan arah. Secara opsional, parameter dapat ditetapkan nilai default.

Bagian berikut ini menyediakan informasi tentang meneruskan nilai ke parameter dan tentang bagaimana masing-masing atribut parameter digunakan selama panggilan prosedur.

Catatan

AdventureWorks Lihat rangkaian database sampel untuk latihan artikel ini. Untuk informasi selengkapnya, lihat Database sampel AdventureWorks.

Meneruskan nilai ke dalam parameter

Nilai parameter yang disediakan dengan panggilan prosedur harus konstanta atau variabel; nama fungsi tidak dapat digunakan sebagai nilai parameter. Variabel dapat ditentukan pengguna atau variabel sistem seperti @@spid.

Contoh berikut menunjukkan meneruskan nilai parameter ke prosedur uspGetWhereUsedProductID. Mereka menggambarkan cara meneruskan parameter sebagai konstanta dan variabel dan juga cara menggunakan variabel untuk meneruskan nilai fungsi.

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

Contoh berikut mengembalikan kesalahan karena fungsi tidak dapat diteruskan sebagai nilai parameter.

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

Sebagai gantinya, gunakan variabel untuk meneruskan nilai fungsi ke parameter , seperti dalam contoh berikut:

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

Tentukan nama parameter

Saat membuat prosedur dan mendeklarasikan nama parameter, nama parameter harus dimulai dengan satu @ karakter dan harus unik dalam cakupan prosedur.

Secara eksplisit menamai parameter dan menetapkan nilai yang sesuai ke setiap parameter dalam panggilan prosedur memungkinkan parameter disediakan dalam urutan apa pun. Misalnya, jika prosedur my_proc mengharapkan tiga parameter bernama @first, , @seconddan @third, nilai yang diteruskan ke prosedur dapat ditetapkan ke nama parameter, seperti: EXECUTE my_proc @second = 2, @first = 1, @third = 3;.

Catatan

Jika satu nilai parameter disediakan dalam formulir @parameter = value, semua parameter berikutnya harus disediakan dengan cara ini. Jika nilai parameter tidak diteruskan dalam formulir @parameter = value, nilai harus disediakan dalam urutan yang identik (kiri ke kanan) karena parameter tercantum dalam pernyataan CREATE PROCEDURE. Ini adalah praktik yang baik untuk menentukan nama parameter, baik untuk keterbacaan dan kompatibilitas yang unggul dengan versi prosedur yang disimpan di masa mendatang.

Peringatan

Parameter apa pun yang diteruskan dalam formulir @parameter = value dengan parameter salah eja, akan menyebabkan SQL Server menghasilkan kesalahan dan mencegah eksekusi prosedur.

Tentukan jenis data parameter

Parameter harus didefinisikan dengan jenis data ketika dinyatakan dalam pernyataan CREATE PROCEDURE. Jenis data parameter menentukan jenis dan rentang nilai yang diterima untuk parameter saat prosedur dipanggil. Misalnya, jika Anda menentukan parameter dengan jenis data kecil , hanya nilai numerik mulai dari 0 hingga 255 yang diterima saat diteruskan ke parameter tersebut. Kesalahan dikembalikan jika prosedur dijalankan dengan nilai yang tidak kompatibel dengan jenis data.

Tentukan nilai default parameter

Parameter dianggap opsional jika parameter memiliki nilai default yang ditentukan saat dinyatakan. Tidak perlu memberikan nilai untuk parameter opsional dalam panggilan prosedur.

Nilai default parameter digunakan saat:

  • Tidak ada nilai untuk parameter yang ditentukan dalam panggilan prosedur.
  • Kata kunci DEFAULT ditentukan sebagai nilai dalam panggilan prosedur.

Catatan

Jika nilai default adalah string karakter yang berisi kosong atau tanda baca yang disematkan, atau jika dimulai dengan angka (misalnya, 6abc), string harus diapit dalam tanda kutip lurus tunggal.

Catatan

Parameter default tidak didukung di Azure Synapse Analytics atau Analytics Platform System (PDW).

Jika tidak ada nilai yang dapat ditentukan dengan tepat sebagai default untuk parameter , tentukan NULL sebagai default. Sebaiknya prosedur mengembalikan pesan yang disesuaikan jika prosedur dijalankan tanpa nilai untuk parameter .

Contoh berikut membuat uspGetSalesYTD prosedur dengan satu parameter input, @SalesPerson. NULL ditetapkan sebagai nilai default untuk parameter dan digunakan dalam pernyataan penanganan kesalahan untuk mengembalikan pesan kesalahan kustom untuk kasus ketika prosedur dijalankan tanpa nilai untuk @SalesPerson parameter.

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

Contoh berikut menjalankan prosedur. Pernyataan pertama menjalankan prosedur tanpa menentukan nilai input. Hal ini menyebabkan pernyataan penanganan kesalahan dalam prosedur mengembalikan pesan kesalahan kustom. Pernyataan kedua menyediakan nilai input dan mengembalikan tataan hasil yang diharapkan.

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

Meskipun parameter yang defaultnya telah disediakan dapat dihilangkan, daftar parameter yang tidak dapat diubah ke null hanya dapat dipotong. Misalnya, jika prosedur memiliki lima parameter, tanpa menentukan nama parameter dengan @parameter = valueparameter , parameter keempat dan kelima dapat dihilangkan. Namun, parameter keempat tidak dapat dilewati selama parameter kelima disertakan, kecuali parameter disediakan dalam formulir @parameter = value.

Tentukan beberapa parameter dengan nilai default

Anda dapat menghilangkan parameter jika Anda menentukan nama parameter. Pertimbangkan prosedur tersimpan berikut dengan beberapa parameter opsional dengan NULL nilai default.

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

Anda dapat menentukan atau menghilangkan parameter dengan nilai default, seperti yang ditunjukkan oleh serangkaian contoh berikut, selama masing-masing disediakan dengan nama parameternya dalam formulir @parameter = value:

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

Contoh berikut tidak valid sintaks T-SQL, karena semua parameter berikutnya harus disediakan dengan cara yang sama, setelah nama parameter disediakan. Menyediakan nama parameter untuk semua nilai selalu direkomendasikan dan mencegah kesalahan dan kebingungan.

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

Tentukan arah parameter

Arah parameter adalah input, nilai diteruskan ke isi prosedur, atau output, prosedur mengembalikan nilai ke program panggilan. Defaultnya adalah parameter input.

Untuk menentukan parameter output, kata kunci OUTPUT harus ditentukan dalam definisi parameter dalam pernyataan CREATE PROCEDURE. Prosedur menampilkan nilai saat ini dari parameter output ke program panggilan ketika prosedur keluar. Program panggilan juga harus menggunakan kata kunci OUTPUT saat menjalankan prosedur untuk menyimpan nilai parameter dalam variabel yang dapat digunakan dalam program panggilan.

Contoh berikut membuat Production.usp_GetList prosedur, yang mengembalikan daftar produk yang memiliki harga yang tidak melebihi jumlah yang ditentukan. Contoh menunjukkan penggunaan beberapa pernyataan SELECT dan beberapa parameter OUTPUT. Parameter OUTPUT memungkinkan prosedur eksternal, batch, atau lebih dari satu pernyataan Transact-SQL untuk mengakses nilai yang ditetapkan selama eksekusi prosedur.

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

Jalankan usp_GetList untuk mengembalikan daftar produk Adventure Works (Bikes) yang harganya kurang dari $700. Parameter @cost OUTPUT dan @compareprices digunakan dengan bahasa kontrol aliran untuk mengembalikan pesan di jendela Pesan .

Catatan

Variabel OUTPUT harus didefinisikan selama pembuatan prosedur dan juga selama penggunaan variabel. Nama parameter dan nama variabel tidak harus cocok. Namun, jenis data dan pemosisian parameter harus cocok (kecuali @listprice = variable digunakan).

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)))+'.';

Berikut adalah kumpulan hasil parsial:

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.

Konten terkait