Lesson 0020 · Database · ±30 menit (15 konsep + kuis, 15 hands-on) · Hari 4

Stored Procedure: parameter, `OUTPUT`, `TRY/CATCH`, dan memanggilnya dari .NET

Menulis SP yang benar, menjawab "kapan pakai SP?" dengan seimbang, dan memanggilnya dari EF Core atau Dapper tanpa celah SQL injection

Target lesson ini: Anda bisa menjawab pertanyaan interview "Kapan Anda memakai Stored Procedure dibanding query dari aplikasi, dan bagaimana memanggilnya dari .NET dengan aman?" tanpa melihat catatan, dan bisa menulis usp_AdjustStock dengan parameter OUTPUT, TRY/CATCH, dan THROW.

Lanjutan lesson 0019: tabel dbo.Category dan dbo.Product di ProductCatalogLab. Padanan Node: Stored Procedure jarang dipakai di proyek Node/Prisma, tapi di perusahaan .NET yang punya DBA, SP sering ada di codebase lama. Jadi interviewer biasanya menguji dua hal: bisakah Anda membaca/menulis SP, dan apakah pendapat Anda seimbang, tidak "SP selalu baik" atau "SP selalu buruk".

1. Parameter, default, `OUTPUT`, dan `RETURN`

Stored Procedure adalah "a group of one or more Transact-SQL statements" yang bisa menerima parameter input, mengembalikan nilai lewat parameter output, dan mengembalikan "a status value to a calling program to indicate success or failure". ^[1]

CREATE OR ALTER PROCEDURE dbo.usp_GetProductsByCategory
    @CategoryId int,
    @MinPrice   decimal(18, 2) = 0,      -- default: boleh tidak diisi
    @TotalCount int = NULL OUTPUT        -- dikirim balik ke pemanggil
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (5) p.Id, p.Name, p.Price, p.Stock, p.CategoryId
    FROM dbo.Product AS p
    WHERE p.CategoryId = @CategoryId AND p.Price >= @MinPrice
    ORDER BY p.Price DESC, p.Id;

    SELECT @TotalCount = COUNT(*) FROM dbo.Product
    WHERE CategoryId = @CategoryId AND Price >= @MinPrice;
    RETURN 0;                            -- status code (int), bukan data
END;
GO
DECLARE @n int;
EXEC dbo.usp_GetProductsByCategory @CategoryId = 6, @TotalCount = @n OUTPUT;
SELECT @n AS TotalCount;                 -- 20

Default: "If a default value is defined for a parameter, the procedure can be executed without specifying a value for that parameter. The default value must be a constant or it can be NULL." ^[2]
OUTPUT ditulis dua kali: "The OUTPUT variable must be defined when the procedure is created and also when the variable is used." Lupa OUTPUT di EXEC tidak membuat error, nilainya saja yang tidak kembali (lab bagian 1c). ^[2]
Tiga cara mengembalikan sesuatu: result set (SELECT, boleh lebih dari satu), parameter OUTPUT (nilai tunggal), dan RETURN (hanya integer status).
Best practice dari dokumentasi: SET NOCOUNT ON sebagai statement pertama (mematikan pesan "rows affected"; dokumen jujur menyebut "There is no measurable performance benefit however on today's hardware"), selalu pakai nama schema (dbo.), hindari SELECT *. Jangan beri awalan sp_: "Because system procedures start with the prefix sp_, don't use this prefix". ^[2] ^[1]

Latihan 1 — Parameter dan OUTPUT

Jawab tanpa scroll ke atas. Soal yang sama akan muncul lagi, diacak, di review database 0024.

Kuis di lesson ini adalah feedback cepat, bukan bukti penguasaan; yang dicatat hanya hasil review 0024.

2. `TRY/CATCH` + `THROW`

CREATE OR ALTER PROCEDURE dbo.usp_UpdatePrice
    @ProductId int, @NewPrice decimal(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                   -- error apa pun me-rollback transaction
    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 Price = @NewPrice WHERE Id = @ProductId;  -- CHECK Price > 0
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;                           -- lempar ulang error asli ke pemanggil
    END CATCH
END;

TRY...CATCH mirip try/catch di C#: "similar to the exception handling in the C# and Visual C++ languages", dan menangkap error "that have a severity higher than 10 that don't close the database connection". Di dalam CATCH tersedia ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), dan kawan-kawan. ^[3]
Jebakan utama: "Errors trapped by a CATCH block aren't returned to the calling application." CATCH yang hanya ROLLBACK membuat aplikasi mengira semuanya berhasil. Karena itu akhiri CATCH dengan THROW;. Tanpa parameter, THROW "must appear inside a CATCH block. This causes the caught exception to be raised." ^[3] ^[4]
THROW vs RAISERROR: "New applications should use THROW instead of RAISERROR." ^[5] Nomor error THROW "must be greater than or equal to 50,000", tidak perlu terdaftar di sys.messages, severity-nya selalu 16, dan berbeda dengan RAISERROR, THROW menghormati SET XACT_ABORT. Statement sebelum THROW wajib diakhiri titik koma. ^[4]
Transaction: dokumentasi menyarankan "Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible", karena transaction panjang berarti lock lebih lama dan risiko deadlock lebih besar. ^[2] XACT_ABORT, @@TRANCOUNT, dan deadlock dibahas di lesson 0021.

Padanan Node: polanya sama dengan try { await tx... } catch (e) { await tx.rollback(); throw e; }. Menelan error di CATCH sama buruknya dengan catch {} kosong.

Latihan 2 — TRY/CATCH dan THROW

3. Stored Procedure vs function

	Stored Procedure	Scalar function	Table-valued function
Mengubah data (INSERT/UPDATE/DELETE)	Ya	Tidak	Tidak
Dipakai di dalam query	Tidak, hanya `EXEC`	Ya, sebagai ekspresi di SELECT/WHERE	Ya, di FROM (bisa di-JOIN)
Hasil	0..n result set, `OUTPUT`, `RETURN` int	satu nilai	satu tabel
TRY/CATCH, transaction, dynamic SQL, temp table	Ya	Tidak	Tidak

Batasan function: "User-defined functions can't be used to perform actions that modify the database state", "can't return multiple result sets. Use a stored procedure if you need to return multiple result sets", "A UDF doesn't support TRY...CATCH, @ERROR, or RAISERROR", dan "can't make use of dynamic SQL or temp tables." ^[6]
Scalar function mengembalikan "a single data value"; inline table-valued function tidak punya body, "the table is the result set of a single SELECT statement". ^[7] SP tidak bisa dipanggil dari dalam query: "You can't execute a stored procedure in a common table expression." ^[11]
Jebakan performa: "Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing". ^[2] Sejak SQL Server 2019, sebagian scalar function di-inline otomatis ^[12], tapi jawaban aman di interview tetap: untuk logika yang dipakai ulang di query, pilih inline TVF.

Latihan 3 — SP atau function?

4. SP vs query dari aplikasi (EF Core)

Aspek	Stored Procedure	Query dari aplikasi (EF Core, berparameter)
Performa	"only the call to execute the procedure is sent across the network"; plan dibuat saat eksekusi pertama lalu dipakai ulang ^[1]	plan query berparameter juga di-cache; untuk CRUD biasa bedanya kecil
Keamanan	user bisa diberi izin menjalankan SP "even if the users and programs don't have direct permissions on those underlying objects"; parameter "helps guard against SQL injection" ^[1]	LINQ dan `FromSql` selalu berparameter; aman selama tidak memakai raw SQL dengan string gabungan
Testability	logika di T-SQL, sulit di-unit-test (butuh database sungguhan)	logika di C# Service, bisa di-unit-test (lesson 0009–0010)
Versioning	di-deploy lewat skrip database, terpisah dari rilis aplikasi	ikut kode aplikasi, code review, dan Migration

Jangan bilang "SP pasti lebih cepat". Plan memang dipakai ulang, tapi "If there are significant changes to the tables or data referenced by the procedure, the precompiled plan might actually cause the procedure to perform slower." ^[1] Itulah parameter sniffing, yang akan Anda lihat sendiri di lesson 0023 dengan Category Elektronik (90.000 Product) vs Koleksi Langka (20 Product).

Jawaban seimbang: EF Core/LINQ sebagai default untuk CRUD; SP untuk operasi set-based berat, query yang sulit diekspresikan dengan LINQ, atau bila DBA mewajibkan akses lewat SP. EF Core sendiri menyebut raw SQL berguna "if the query you want can't be expressed using LINQ, or if a LINQ query causes EF to generate inefficient SQL." ^[8]

Latihan 4 — Kapan SP?

5. Memanggil SP dari .NET: EF Core dan Dapper

Potongan kode C# di bawah hanya untuk dibaca dan diucapkan di interview; tidak ada project .NET baru di lesson ini. Mini project memakai EF Core 8.

EF Core 8: `FromSql` dan `ExecuteSql`

// SP yang mengembalikan entity: categoryId menjadi DbParameter, bukan teks SQL.
var products = await context.Products
    .FromSql($"EXECUTE dbo.usp_GetProductsByCategory @CategoryId={categoryId}, @MinPrice={minPrice}")
    .AsNoTracking()
    .ToListAsync();          // jangan tambah .Where/.OrderBy setelah EXEC

// SP tanpa result set: int = rows affected, atau -1 bila SP memakai SET NOCOUNT ON.
int rows = await context.Database.ExecuteSqlAsync(
    $"EXECUTE dbo.usp_UpdatePrice @ProductId={id}, @NewPrice={newPrice}");

// BERBAHAYA: $"..." sudah menjadi string biasa sebelum EF melihatnya -> input masuk ke teks SQL.
var bad = context.Products.FromSqlRaw($"SELECT * FROM dbo.Product WHERE Name = '{userInput}'");
// AMAN bila terpaksa raw: placeholder + argumen terpisah.
var ok  = context.Products.FromSqlRaw("SELECT * FROM dbo.Product WHERE Name = {0}", userInput);

Kenapa interpolasi di FromSql aman: "While this syntax may look like regular C# string interpolation, the supplied value is wrapped in a DbParameter". FromSql menerima FormattableString; FromSqlRaw menerima string. "The FromSql and FromSqlInterpolated methods are safe against SQL injection ... However, the FromSqlRaw method can be vulnerable to SQL injection attacks, if improperly used." ^[8]
FromSql ada sejak EF Core 7 (versi lama: FromSqlInterpolated), hanya bisa dipakai langsung di DbSet, dan hasilnya harus berisi semua kolom entity. Itu sebabnya usp_GetProductsByCategory mengembalikan CategoryId juga. ^[8]
Tidak bisa di-compose: "SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid SQL." Filter di dalam SP, atau pakai AsAsyncEnumerable() lalu filter di memori. ^[8]
"ExecuteSql ... returns the number of rows modified. ExecuteSql protects against SQL injection by using safe parameterization, just like FromSql". Untuk hasil non-entity (scalar, DTO) ada Database.SqlQuery<T>. ^[8]
Jebakan SET NOCOUNT ON: angka itu berasal dari ExecuteNonQuery ADO.NET. "When SET NOCOUNT ON is set on the connection ... the rows affected by individual statements stop contributing to the count of rows affected ... If no statements are detected that contribute to the count, the return value is -1." Semua SP di lesson ini memakai SET NOCOUNT ON, jadi ExecuteSqlAsync mengembalikan -1. Hasil SP yang penting (mis. @NewStock) diambil lewat parameter OUTPUT atau return value, bukan dari angka ini. ^[13]

Dapper: `CommandType.StoredProcedure`

using var conn = new SqlConnection(connectionString);
var p = new DynamicParameters();
p.Add("@CategoryId", 6);
p.Add("@TotalCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

var products = (await conn.QueryAsync<ProductDto>(
    "dbo.usp_GetProductsByCategory", p, commandType: CommandType.StoredProcedure)).ToList();
int total = p.Get<int>("@TotalCount");   // baca setelah result set selesai dibaca

// SP tanpa result set, parameter input saja: cukup objek anonim.
await conn.ExecuteAsync("dbo.usp_UpdatePrice", new { ProductId = id, NewPrice = newPrice },
    commandType: CommandType.StoredProcedure);

Dapper adalah library yang "will enhance your ADO.NET connections via extension methods on your DbConnection instance": Query<T> untuk membaca, Execute untuk perintah. "Dapper fully supports stored procs", dan DynamicParameters dipakai untuk parameter Output dan ReturnValue. ^[10]
Padanan Node: Dapper ≈ pg/mssql (SQL tulis sendiri + mapping ke objek); EF Core FromSql ≈ Prisma $queryRaw (tagged template, otomatis berparameter); FromSqlRaw ≈ $queryRawUnsafe.

SQL injection, sekali lagi

"SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of the Database Engine for parsing and execution." Parameter mencegahnya karena "input is treated as a literal value instead of as executable code." SP bukan jaminan otomatis: "Stored procedures might be susceptible to SQL injection if they use unfiltered input", misalnya SP yang menggabungkan string lalu EXEC (@sql). ^[9] Lab bagian 4 memperlihatkan input x' OR 1 = 1 -- yang menemukan 100.000 baris lewat string gabungan, dan 0 baris lewat sp_executesql berparameter.

Latihan 5 — EF Core, Dapper, dan injection

6. Hands-on (±15 menit)

Pastikan container jalan (docker start sql2022) dan setup lab sudah dijalankan (6 Category, 100000 Product).
Buka dotnet/lab/0020-stored-procedure.sql di Azure Data Studio atau VS Code mssql (atau salin dari blok di bawah), koneksi localhost,1433, user sa.
Jalankan per bagian (pilih blok sampai GO, lalu Run). Sebelum menjalankan, tebak dulu hasilnya:
- Bagian 1: berapa ReturnStatus dan TotalCount di setiap panggilan? Kenapa 1c menghasilkan NULL?
- Bagian 2: nomor error apa yang sampai ke CATCH luar di 2b dan 2c? Apakah ada transaction yang menggantung?
- Bagian 3: CREATE function terakhir sengaja gagal. Baca pesan error-nya.
- Bagian 4: lihat hasil PRINT @bad. Kenapa string gabungan menemukan semua baris?

Lewat sqlcmd di dalam container: docker cp dotnet/lab/0020-stored-procedure.sql sql2022:/tmp/0020.sql lalu docker exec sql2022 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Dev_Passw0rd!' -C -i /tmp/0020.sql. Error di akhir bagian 3 memang disengaja.

-- Lesson 0020 · Stored Procedure: parameter, default, OUTPUT, RETURN, TRY/CATCH + THROW,
-- SP vs function, dan kenapa parameter mencegah SQL injection.
-- Jalankan setelah dotnet/lab/00-setup-product-catalog-lab.sql. Boleh dijalankan ulang (CREATE OR ALTER).
-- Jalankan per bagian (blok sampai GO), lalu baca hasilnya sebelum lanjut.

USE ProductCatalogLab;
SET NOCOUNT ON;
GO

-- 1. Parameter input, default, OUTPUT, dan RETURN --------------------------------------------
-- Nama memakai awalan usp_, bukan sp_ (sp_ dipakai system procedure).
CREATE OR ALTER PROCEDURE dbo.usp_GetProductsByCategory
    @CategoryId int,
    @MinPrice   decimal(18, 2) = 0,        -- default: pemanggil boleh tidak mengisinya
    @TotalCount int = NULL OUTPUT          -- OUTPUT: nilainya dikirim balik ke pemanggil
AS
BEGIN
    SET NOCOUNT ON;                        -- tidak mengirim pesan "(n rows affected)"

    IF NOT EXISTS (SELECT 1 FROM dbo.Category WHERE Id = @CategoryId)
    BEGIN
        SET @TotalCount = 0;
        RETURN 1;                          -- status code: Category tidak ada
    END;

    -- Result set: 5 Product termahal (semua kolom Entity Product, supaya bisa dipetakan EF Core).
    SELECT TOP (5) p.Id, p.Name, p.Price, p.Stock, p.CategoryId
    FROM dbo.Product AS p
    WHERE p.CategoryId = @CategoryId AND p.Price >= @MinPrice
    ORDER BY p.Price DESC, p.Id;

    SELECT @TotalCount = COUNT(*)
    FROM dbo.Product AS p
    WHERE p.CategoryId = @CategoryId AND p.Price >= @MinPrice;

    RETURN 0;                              -- status code: sukses
END;
GO

DECLARE @total int, @status int;

-- a) @MinPrice tidak diisi, jadi memakai default 0. Koleksi Langka (Id 6) punya 20 Product.
EXEC @status = dbo.usp_GetProductsByCategory @CategoryId = 6, @TotalCount = @total OUTPUT;
SELECT @status AS ReturnStatus, @total AS TotalCount;           -- 0 dan 20

-- b) Semua parameter diisi dengan nama (named parameter).
EXEC @status = dbo.usp_GetProductsByCategory @CategoryId = 2, @MinPrice = 4500, @TotalCount = @total OUTPUT;
SELECT @status AS ReturnStatus, @total AS TotalCount;

-- c) Lupa menulis OUTPUT di pemanggil: tidak error, tapi nilainya TIDAK kembali.
SET @total = NULL;
EXEC dbo.usp_GetProductsByCategory @CategoryId = 6, @TotalCount = @total;
SELECT @total AS TotalCountTanpaOutput;                         -- NULL

-- d) Category tidak ada: RETURN 1.
EXEC @status = dbo.usp_GetProductsByCategory @CategoryId = 999, @TotalCount = @total OUTPUT;
SELECT @status AS ReturnStatus, @total AS TotalCount;           -- 1 dan 0
GO

-- 2. TRY/CATCH + THROW -----------------------------------------------------------------------
CREATE OR ALTER PROCEDURE dbo.usp_UpdatePrice
    @ProductId int,
    @NewPrice  decimal(18, 2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;                     -- error apa pun membuat transaction di-rollback

    BEGIN TRY
        BEGIN TRANSACTION;

        IF NOT EXISTS (SELECT 1 FROM dbo.Product WHERE Id = @ProductId)
            THROW 50001, N'Product tidak ditemukan.', 1;   -- error buatan sendiri: nomor >= 50000

        -- CK_Product_Price menolak Price <= 0 dengan error 547.
        UPDATE dbo.Product SET Price = @NewPrice WHERE Id = @ProductId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;                             -- lempar ulang error asli; tanpa ini pemanggil tidak tahu ada error
    END CATCH
END;
GO

-- a) Berhasil. Dibungkus transaction luar lalu di-ROLLBACK supaya data lab tidak berubah
--    (nested transaction dibahas di lesson 0021).
BEGIN TRANSACTION;
EXEC dbo.usp_UpdatePrice @ProductId = 1, @NewPrice = 123.45;
SELECT Id, Name, Price FROM dbo.Product WHERE Id = 1;           -- 123.45
ROLLBACK TRANSACTION;
SELECT Id, Name, Price FROM dbo.Product WHERE Id = 1;           -- kembali 80.19
GO

-- b) Product tidak ada: THROW 50001 di dalam SP, ditangkap CATCH di SP, dilempar ulang ke sini.
BEGIN TRY
    EXEC dbo.usp_UpdatePrice @ProductId = 999999, @NewPrice = 10;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity,
           ERROR_PROCEDURE() AS InProcedure, ERROR_MESSAGE() AS Message;   -- 50001, 16
END CATCH;
GO

-- c) Price 0: CHECK constraint gagal (547), transaction di-rollback, error asli sampai ke pemanggil.
BEGIN TRY
    EXEC dbo.usp_UpdatePrice @ProductId = 1, @NewPrice = 0;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity,
           ERROR_PROCEDURE() AS InProcedure, ERROR_MESSAGE() AS Message;   -- 547
END CATCH;
SELECT @@TRANCOUNT AS OpenTransactions;                         -- 0: tidak ada transaction menggantung
GO

-- 3. SP vs function --------------------------------------------------------------------------
-- Scalar function: mengembalikan satu nilai, dipakai sebagai ekspresi di SELECT atau WHERE.
CREATE OR ALTER FUNCTION dbo.fn_PriceWithTax (@Price decimal(18, 2))
RETURNS decimal(18, 2)
AS
BEGIN
    RETURN ROUND(@Price * 1.11, 2);        -- PPN 11%
END;
GO

-- Inline table-valued function: satu SELECT, dipakai di FROM seperti tabel (bisa di-JOIN).
CREATE OR ALTER FUNCTION dbo.fn_ProductsByCategory (@CategoryId int)
RETURNS TABLE
AS
RETURN
    SELECT p.Id, p.Name, p.Price, p.Stock, p.CategoryId
    FROM dbo.Product AS p
    WHERE p.CategoryId = @CategoryId;
GO

SELECT TOP (3) f.Name, f.Price, dbo.fn_PriceWithTax(f.Price) AS PriceWithTax, c.Name AS CategoryName
FROM dbo.fn_ProductsByCategory(6) AS f
INNER JOIN dbo.Category AS c ON c.Id = f.CategoryId
ORDER BY f.Price DESC;

-- SP tidak bisa dipakai di FROM atau di dalam SELECT; SP hanya dijalankan dengan EXEC.
-- Baris berikut error bila di-uncomment:
-- SELECT * FROM dbo.usp_GetProductsByCategory(6);
GO

-- Function tidak boleh mengubah data. CREATE ini SENGAJA gagal
-- (error 443: UPDATE adalah operator dengan efek samping, tidak boleh di dalam function).
CREATE OR ALTER FUNCTION dbo.fn_ResetStock (@ProductId int)
RETURNS int
AS
BEGIN
    UPDATE dbo.Product SET Stock = 0 WHERE Id = @ProductId;
    RETURN 0;
END;
GO

-- 4. Kenapa parameter mencegah SQL injection -------------------------------------------------
-- Input jahat dari "form pencarian".
DECLARE @input nvarchar(200) = N'x'' OR 1 = 1 --';

-- BERBAHAYA: input digabung ke teks SQL. Sama seperti FromSqlRaw dengan string interpolation.
DECLARE @bad nvarchar(max) = N'SELECT COUNT(*) AS Ditemukan FROM dbo.Product WHERE Name = N''' + @input + N'''';
PRINT @bad;                                -- OR 1 = 1 kini bagian dari query
EXEC (@bad);                               -- 100000: semua baris lolos

-- AMAN: input dikirim sebagai parameter dan diperlakukan sebagai nilai literal, bukan kode.
EXEC sys.sp_executesql
    N'SELECT COUNT(*) AS Ditemukan FROM dbo.Product WHERE Name = @Name',
    N'@Name nvarchar(200)',
    @Name = @input;                        -- 0: tidak ada Product bernama "x' OR 1 = 1 --"
GO

7. Latihan tulis query

Tulis sendiri di editor SQL, jalankan di lab, baru buka jawaban. Tidak dinilai otomatis; bandingkan hasil dan bentuk kodenya.

A. Tulis Stored Procedure dbo.usp_AdjustStock dengan parameter @ProductId int, @Delta int (positif = barang masuk, negatif = barang keluar), dan @NewStock int OUTPUT. SP harus menolak Stock negatif dengan THROW nomor sendiri (jangan hanya mengandalkan CHECK constraint), menolak Product yang tidak ada, memakai transaction dan TRY/CATCH, lalu mengembalikan Stock baru lewat OUTPUT. Uji dengan Product Id 1 (Stock awal 31): kurangi 5, lalu coba kurangi 100000.

Lihat jawaban

CREATE OR ALTER PROCEDURE dbo.usp_AdjustStock
    @ProductId int,
    @Delta     int,                      -- positif = masuk, negatif = keluar
    @NewStock  int = NULL OUTPUT
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;

        -- Satu UPDATE dengan syarat: cek dan ubah terjadi bersamaan.
        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;
    END CATCH
END;
GO

DECLARE @s int;
EXEC dbo.usp_AdjustStock @ProductId = 1, @Delta = -5, @NewStock = @s OUTPUT;
SELECT @s AS NewStock;                   -- 26

BEGIN TRY
    EXEC dbo.usp_AdjustStock @ProductId = 1, @Delta = -100000, @NewStock = @s OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS Message;   -- 50002
END CATCH;

EXEC dbo.usp_AdjustStock @ProductId = 1, @Delta = 5, @NewStock = @s OUTPUT;   -- kembalikan ke 31
SELECT @s AS NewStock;
GO

Poin yang dinilai interviewer: OUTPUT di definisi dan di EXEC; XACT_ABORT ON; THROW dengan nomor ≥ 50000 dan pesan yang jelas; CATCH yang ROLLBACK lalu THROW;. Kenapa tidak cukup CHECK constraint? Error 547 dari constraint tetap menjadi penjaga terakhir, tapi pesan 50002 lebih jelas dan mudah dipetakan aplikasi ke response 400/409. UPDATE dengan syarat Stock + @Delta >= 0 menghindari pola "baca dulu, lalu ubah" yang bisa balapan antar-request (lesson 0021). Dari EF Core: await context.Database.ExecuteSqlAsync($"EXEC dbo.usp_AdjustStock @ProductId={id}, @Delta={delta}"); nomor 50002 bisa dibaca dari SqlException.Number.

B. Tulis inline table-valued function dbo.fn_LowStockProducts(@MaxStock int) yang mengembalikan Id, Name, Stock, dan CategoryId untuk Product dengan Stock <= @MaxStock. Lalu pakai function itu di FROM, JOIN ke Category, dan hitung jumlah Product yang stoknya habis (@MaxStock = 0) per Category, urut dari yang terbanyak.

Lihat jawaban

CREATE OR ALTER FUNCTION dbo.fn_LowStockProducts (@MaxStock int)
RETURNS TABLE
AS
RETURN
    SELECT p.Id, p.Name, p.Stock, p.CategoryId
    FROM dbo.Product AS p
    WHERE p.Stock <= @MaxStock;
GO

SELECT c.Name, COUNT(*) AS OutOfStockCount
FROM dbo.fn_LowStockProducts(0) AS f
INNER JOIN dbo.Category AS c ON c.Id = f.CategoryId
GROUP BY c.Name
ORDER BY OutOfStockCount DESC;
GO

Hasilnya harus sama dengan latihan B di lesson 0019 tanpa HAVING: Elektronik 180, Olahraga 19, Koleksi Langka 1. Function dipilih, bukan SP, karena hasilnya harus bisa ditulis di FROM dan di-JOIN; SP hanya bisa di-EXEC. Inline TVF juga tidak punya jebakan "per baris" seperti scalar function, karena definisinya digabung ke query pemanggil.

Latihan 6 — Ucapkan dengan suara keras

Pasang timer dan jawab dengan suara keras, seolah sedang interview. Jangan buka jawaban contoh sebelum selesai.

"Kapan Anda memakai Stored Procedure dibanding query dari aplikasi lewat EF Core? Jelaskan trade-off-nya." (60–90 detik)

Bandingkan dengan jawaban contoh dan checklist-nya

"Default saya EF Core dengan LINQ untuk CRUD dan query biasa, karena logikanya ada di C#, bisa di-unit-test, ikut code review dan versioning aplikasi, dan query-nya tetap berparameter jadi aman dari SQL injection dan plan-nya di-cache. Saya memilih Stored Procedure untuk kasus khusus: operasi set-based berat yang lebih efisien dikerjakan di server karena hanya panggilan EXEC yang lewat jaringan, query yang tidak bisa diekspresikan LINQ atau yang SQL hasil EF-nya tidak efisien, atau saat DBA mewajibkan akses lewat SP, karena user bisa diberi izin EXECUTE tanpa izin langsung ke tabel. SP tidak otomatis lebih cepat: plan-nya dibuat saat eksekusi pertama dan dipakai ulang, yang bisa merugikan kalau datanya timpang, yaitu parameter sniffing. Biayanya: logika di SP sulit di-unit-test dan perubahannya di-deploy lewat skrip database, terpisah dari aplikasi. Kalau dipakai, saya panggil dengan FromSql/ExecuteSql atau Dapper dengan parameter."

Checklist: apakah Anda menyebut…

default EF Core/LINQ untuk CRUD: logika di C#, mudah di-unit-test dan di-review?
query berparameter dari EF juga aman dari injection dan plan-nya di-cache, jadi SP tidak otomatis lebih cepat?
kasus SP: operasi set-based berat atau query yang sulit di LINQ (hanya EXEC yang lewat jaringan)?
keamanan: izin EXECUTE pada SP tanpa izin langsung ke tabel?
biaya SP: sulit di-unit-test, versioning/deploy terpisah dari kode aplikasi?
plan dipakai ulang bisa merugikan (parameter sniffing), atau cara memanggil SP dengan parameter?

Lulus kalau minimal 4 dari 6.

"Bagaimana Anda memanggil Stored Procedure dari .NET tanpa membuka celah SQL injection? Kenapa string interpolation di FromSqlRaw berbahaya?" (60–90 detik)

Bandingkan dengan jawaban contoh dan checklist-nya

"SQL injection terjadi kalau input user ikut menjadi teks SQL yang di-parse engine. Kuncinya, input dikirim sebagai parameter, sehingga diperlakukan sebagai nilai literal, bukan kode. Di EF Core 8 saya pakai context.Products.FromSql($"EXEC dbo.usp_GetProductsByCategory @CategoryId={categoryId}") untuk SP yang mengembalikan entity. Walau terlihat seperti interpolasi, FromSql menerima FormattableString dan setiap nilai dibungkus menjadi DbParameter. Setelah EXEC saya tidak menambah Where, karena SQL Server tidak bisa meng-compose di atas SP. Untuk SP tanpa result set saya pakai Database.ExecuteSqlAsync; angka kembaliannya rows affected, atau -1 karena SP memakai SET NOCOUNT ON, jadi hasil SP saya ambil lewat parameter OUTPUT. Bahayanya FromSqlRaw($"... '{name}'"): string sudah jadi sebelum EF melihatnya, jadi input masuk ke teks SQL. Kalau terpaksa raw, pakai placeholder {0} atau SqlParameter. Di Dapper saya pakai commandType: CommandType.StoredProcedure dengan objek anonim atau DynamicParameters, termasuk ParameterDirection.Output untuk parameter OUTPUT. SP sendiri juga bisa rentan kalau di dalamnya menggabungkan string untuk dynamic SQL; pakai sp_executesql dengan parameter."

Checklist: apakah Anda menyebut…

injection = input menjadi bagian teks SQL; parameter membuat input diperlakukan sebagai nilai literal?
EF Core FromSql menerima FormattableString: nilai interpolasi menjadi DbParameter?
FromSqlRaw + $"..." menempel input ke teks SQL; raw harus pakai placeholder/SqlParameter?
ExecuteSql untuk SP tanpa result set, dan tidak meng-compose Where di atas EXEC?
Dapper: CommandType.StoredProcedure + objek anonim/DynamicParameters, ParameterDirection.Output untuk OUTPUT?
SP pun rentan bila menggabungkan string untuk dynamic SQL; pakai sp_executesql dengan parameter?

Lulus kalau minimal 4 dari 6.

"Tulis di kertas usp_AdjustStock yang menolak Stock negatif, lalu jelaskan error handling-nya." (90 detik, tanpa melihat latihan A)

Bandingkan dengan jawaban contoh dan checklist-nya

"Parameternya @ProductId, @Delta, dan @NewStock int OUTPUT. Baris pertama SET NOCOUNT ON dan SET XACT_ABORT ON, supaya error apa pun me-rollback transaction. Isinya di dalam BEGIN TRY: BEGIN TRANSACTION, cek Product ada, kalau tidak THROW 50001. Lalu satu UPDATE SET Stock = Stock + @Delta WHERE Id = @ProductId AND Stock + @Delta >= 0; kalau @@ROWCOUNT = 0 berarti hasilnya akan negatif, jadi THROW 50002, N'Stock tidak boleh negatif.', 1. Setelah itu isi @NewStock dan COMMIT. Di BEGIN CATCH: IF @@TRANCOUNT > 0 ROLLBACK, lalu THROW; tanpa parameter untuk melempar ulang error asli, karena error yang ditangkap CATCH tidak sampai ke aplikasi. Saya pakai THROW, bukan RAISERROR, sesuai saran Microsoft untuk kode baru. CHECK constraint Stock >= 0 tetap jadi penjaga terakhir, tapi error 50002 lebih jelas untuk dipetakan aplikasi ke response 400 atau 409."

Checklist: apakah Anda menyebut…

parameter input @ProductId, @Delta, dan @NewStock ber-OUTPUT?
SET NOCOUNT ON dan SET XACT_ABORT ON?
BEGIN TRY + BEGIN TRANSACTION … COMMIT?
menolak Stock negatif dengan THROW nomor ≥ 50000 (cek @@ROWCOUNT atau cek sebelum UPDATE)?
CATCH: IF @@TRANCOUNT > 0 ROLLBACK lalu THROW;, karena error di CATCH tidak sampai ke aplikasi?
THROW, bukan RAISERROR, untuk kode baru; CHECK constraint tetap penjaga terakhir?

Lulus kalau minimal 4 dari 6. Review 0024 akan menanyakan salah satunya lagi.

Bacaan utama (±10 menit) Microsoft Learn: SQL Queries (EF Core). Baca bagian "Passing parameters" dan "Composing with LINQ", lalu bagian "Best practices" di CREATE PROCEDURE.

Ringkasan untuk dicetak: Reference: Stored Procedure · Istilah: GLOSSARY.md (bagian "Database (SQL Server)")

Ada yang belum jelas? Tanyakan ke saya (agent) di terminal, misalnya: "kapan multi-statement TVF lebih buruk dari inline TVF?", "bagaimana membaca parameter OUTPUT dari EF Core dengan SqlParameter?", atau "apa beda XACT_ABORT ON dan OFF saat error?"

Sumber: [1] Stored procedures (Database Engine) · [2] CREATE PROCEDURE · [3] TRY...CATCH · [4] THROW · [5] RAISERROR · [6] Create user-defined functions · [7] User-defined functions · [8] EF Core: SQL Queries · [9] SQL injection · [10] Dapper README · [11] WITH common_table_expression · [12] Scalar UDF inlining · [13] SqlCommand.ExecuteNonQuery

1. Parameter, default, OUTPUT, dan RETURN

Latihan 1 — Parameter dan OUTPUT

2. TRY/CATCH + THROW

Latihan 2 — TRY/CATCH dan THROW

3. Stored Procedure vs function

Latihan 3 — SP atau function?

4. SP vs query dari aplikasi (EF Core)

Latihan 4 — Kapan SP?

5. Memanggil SP dari .NET: EF Core dan Dapper

EF Core 8: FromSql dan ExecuteSql

Dapper: CommandType.StoredProcedure

SQL injection, sekali lagi

Latihan 5 — EF Core, Dapper, dan injection

6. Hands-on (±15 menit)

7. Latihan tulis query

Latihan 6 — Ucapkan dengan suara keras

1. Parameter, default, `OUTPUT`, dan `RETURN`

2. `TRY/CATCH` + `THROW`

EF Core 8: `FromSql` dan `ExecuteSql`

Dapper: `CommandType.StoredProcedure`