T-SQL Stored Procedures in SQL Server for Beginners

Basic stored procedure structure

T-SQL is SQL Server's SQL dialect. Its CREATE PROCEDURE structure has syntax differences from Oracle PL/SQL and MySQL.

CREATE PROCEDURE dbo.TambahPelanggan
    @Nama NVARCHAR(100),
    @Email NVARCHAR(150),
    @PelangganId INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO Pelanggan (Nama, Email)
    VALUES (@Nama, @Email);

    SET @PelangganId = SCOPE_IDENTITY();
END;

Call a procedure with an output parameter

DECLARE @IdBaru INT;

EXEC dbo.TambahPelanggan
    @Nama = N'Rangga',
    @Email = N'rangga@contoh.test',
    @PelangganId = @IdBaru OUTPUT;

SELECT @IdBaru AS IdPelangganBaru;

SCOPE_IDENTITY() returns the last identity value generated in the same scope. Unlike @@IDENTITY, it does not pick up identity values generated in a different scope by a trigger.

SET NOCOUNT ON

SET NOCOUNT ON suppresses affected-row count messages for statements, avoiding unnecessary messages between the application and server.

Handle errors with TRY...CATCH

BEGIN TRY
    BEGIN TRANSACTION;
    -- pernyataan yang berpotensi gagal
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Exercise

Add validation to TambahPelanggan so it raises a custom error with THROW when the email address already exists. The original example retains Indonesian object and parameter names.