Syntax

Berikut merupakan contoh syntax dasar dari SQL Stored Procedure.

CREATE PROCEDURE Procedure-name 
(
     Input parameters ,
     Output Parameters (If required)
)

AS
Begin
     Sql statement used in the stored procedure
End

Berikut merupakan contoh syntax:

CREATE PROCEDURE callCities 
    @city nvarchar(30) 
AS 
SELECT * 
FROM Person.Address 
WHERE city = @city;


//untuk memanggil
EXEC callCities @city = 'London';
atau
EXEC cityProc2 'London';

CREATE PROCEDURE GetstudentnameInOutputVariable
    @studentid INT,                       --Input parameter ,  Studentid of the student
    @studentname VARCHAR(200)  OUT        -- Out parameter declared with the help of OUT keyword
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

Selain itu, Stored Procedure juga dapat mengembalikan suatu nilai (return).

CREATE PROCEDURE test4
    @city varchar(30), @namaCount int OUT
AS 
SELECT @namaCount = count(*) 
FROM city
WHERE nama = @city; 


//cara eksekusi
DECLARE @namaCount int;
EXEC test4 'Justin', @namaCount OUT;
SELECT @namaCount;