Tentukan parameter dalam prosedur tersimpan
Berlaku untuk:
SQL Server Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)
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 dalam 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 untuk 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 bentuk @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 tersimpan 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 ditentukan dengan jenis data saat dideklarasikan 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), itu 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. Ada baiknya 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. Ini menyebabkan pernyataan penanganan kesalahan dalam prosedur mengembalikan pesan kesalahan kustom. Pernyataan kedua memasok 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 ditentukan selama pembuatan prosedur dan juga selama penggunaan variabel. Nama parameter dan nama variabel tidak harus cocok. Namun, jenis data dan penempatan 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 tataan 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.
Langkah berikutnya
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk