Stored Procedure, function, dan memanggil SP dari .NET
Ringkasan satu halaman. Dari Lesson 0020. Skrip lab: dotnet/lab/0020-stored-procedure.sql.
Kerangka SP [1] [3]
CREATE OR ALTER PROCEDURE dbo.usp_AdjustStock -- usp_, bukan sp_ (system procedure)
@ProductId int,
@Delta int,
@NewStock int = NULL OUTPUT -- default boleh konstanta atau NULL
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT 1 FROM dbo.Product WHERE Id = @ProductId)
THROW 50001, N'Product tidak ditemukan.', 1;
UPDATE dbo.Product SET Stock = Stock + @Delta
WHERE Id = @ProductId AND Stock + @Delta >= 0;
IF @@ROWCOUNT = 0
THROW 50002, N'Stock tidak boleh negatif.', 1;
SELECT @NewStock = Stock FROM dbo.Product WHERE Id = @ProductId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW; -- wajib: error di CATCH tidak sampai ke aplikasi
END CATCH
END;
GO
DECLARE @s int, @status int;
EXEC @status = dbo.usp_AdjustStock @ProductId = 1, @Delta = -5, @NewStock = @s OUTPUT; -- OUTPUT di EXEC juga- Mengembalikan: result set (
SELECT, 0..n), parameterOUTPUT,RETURN(int status saja). - Lupa
OUTPUTdi EXEC → tidak error, nilainya tidak kembali. THROW: nomor ≥ 50000, tidak perlu disys.messages, severity 16, menghormatiXACT_ABORT; statement sebelumnya wajib diakhiri;. Kode baru pakai THROW, bukan RAISERROR. [4]- TRY...CATCH menangkap error severity > 10 yang tidak memutus koneksi; tidak menangkap compile error di level yang sama. [2]
- Best practice:
SET NOCOUNT ON, nama schema, tanpaSELECT *, transaction sependek mungkin, jangan bungkus kolom dengan fungsi di WHERE (lesson 0023).
SP vs function [5]
| SP | Scalar UDF | Inline TVF | |
|---|---|---|---|
| Ubah data | Ya | Tidak | Tidak |
| Di dalam query | Tidak (EXEC saja, juga tidak di CTE) | Ekspresi di SELECT/WHERE | Di FROM, bisa di-JOIN |
| Hasil | 0..n result set, OUTPUT, RETURN | 1 nilai | 1 tabel (satu SELECT) |
| TRY/CATCH, transaction, dynamic SQL, temp table | Ya | Tidak | Tidak |
Scalar UDF di SELECT banyak baris = pemrosesan per baris (lambat); SQL Server 2019+ bisa meng-inline sebagian. Untuk logika query yang dipakai ulang → inline TVF.
SP vs query dari aplikasi [6]
| Aspek | SP | EF Core (berparameter) |
|---|---|---|
| Performa | hanya EXEC lewat jaringan; plan dibuat saat eksekusi pertama lalu dipakai ulang (bisa merugikan: parameter sniffing) | plan juga di-cache; CRUD biasa setara |
| Keamanan | izin EXECUTE tanpa izin ke tabel; parameter mencegah injection | selalu berparameter kecuali raw SQL digabung string |
| Testability | sulit di-unit-test | logika di Service C#, mudah di-test |
| Versioning | skrip database, terpisah dari rilis | ikut kode, review, Migration |
Default: EF Core untuk CRUD. SP: set-based berat, query yang sulit di LINQ, atau kebijakan DBA.
Memanggil dari .NET [7] [8]
// EF Core 8 — aman: FormattableString, nilai menjadi DbParameter
var list = await context.Products
.FromSql($"EXECUTE dbo.usp_GetProductsByCategory @CategoryId={categoryId}")
.AsNoTracking().ToListAsync(); // tanpa .Where setelah EXEC
int rows = await context.Database.ExecuteSqlAsync( // -1: SP memakai SET NOCOUNT ON
$"EXECUTE dbo.usp_AdjustStock @ProductId={id}, @Delta={delta}");
// BERBAHAYA // AMAN bila terpaksa raw
context.Products.FromSqlRaw($"... = '{input}'"); context.Products.FromSqlRaw("... = {0}", input);
// Dapper
var p = new DynamicParameters();
p.Add("@CategoryId", 6);
p.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
var rowsDto = await conn.QueryAsync<ProductDto>("dbo.usp_GetProductsByCategory", p,
commandType: CommandType.StoredProcedure);
int total = p.Get<int>("@TotalCount");FromSql: hanya diDbSet, hasil harus memuat semua kolom entity, SP tidak bisa di-compose.ExecuteSql: tanpa result set, mengembalikan rows affected, atau -1 bila SP memakaiSET NOCOUNT ON; hasil SP ambil lewatOUTPUT/return value [10].SqlQuery<T>: scalar/DTO.- SP pun rentan injection bila menggabungkan string ke
EXEC (@sql)→sp_executesqldengan parameter. [9] - Node: Dapper ≈
pg/mssql;FromSql≈ Prisma$queryRaw;FromSqlRaw≈$queryRawUnsafe.
Jawaban 60–90 detik
Default EF Core/LINQ untuk CRUD: logika di C#, bisa di-unit-test, ikut versioning aplikasi, tetap berparameter dan plan-nya di-cache. SP untuk operasi set-based berat, query yang sulit di LINQ,
atau bila akses harus lewat izin EXECUTE. SP tidak otomatis lebih cepat (plan dipakai ulang → parameter sniffing), dan biayanya testability serta deploy terpisah.
Panggil dengan FromSql/ExecuteSql atau Dapper CommandType.StoredProcedure; jangan pernah FromSqlRaw dengan $"...".
Sumber: [1] CREATE PROCEDURE · [2] TRY...CATCH · [3] THROW · [4] RAISERROR · [5] Create user-defined functions · [6] Stored procedures · [7] EF Core: SQL Queries · [8] Dapper README · [9] SQL injection · [10] SqlCommand.ExecuteNonQuery