Menentukan Parameter

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.

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 AdventureWorks2012;  
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  
-- Try to use a function as a parameter value.  
-- This produces an error message.  
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();  
GO  
-- Passing the function value as a variable.  
DECLARE @CheckDate datetime;  
SET @CheckDate = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;  
GO

Menentukan 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, @second, dan @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.

Peringatan

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

Menentukan Tipe 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 tinyint jenis data, 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.

Menentukan 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, 6xxx), string harus diapit dalam tanda kutip lurus tunggal.

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 usp_GetSalesYTD 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 AdventureWorks2012;  
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.usp_GetSalesYTD;  
GO  
-- Run the procedure with an input value.  
EXEC Sales.usp_GetSalesYTD N'Blythe';  
GO

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

Menentukan 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.uspprosedur _GetList , 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 AdventureWorks2012;  
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 OUTPUT @cost 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 variabel @listprice=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.

Lihat Juga

CREATE PROCEDURE (Transact-SQL)

Last updated on 2017-06-13

Bagikan melalui

Menentukan Parameter

Meneruskan Nilai ke Dalam Parameter

Menentukan Nama Parameter

Menentukan Tipe Data Parameter

Menentukan Nilai Default Parameter

Menentukan Arah Parameter

Lihat Juga

Sumber Daya Tambahan: