STORED PROCEDURE & DYNAMIC SQL
B A S I S D ATA L A N J U T
OUTLINE
• Konsep stored procedure
• Membuat stored procedure
• Melewatkan parameter ke stored procedure
• Menjalankan SQL dinamis
KONSEP STORED PROCEDURE
• Stored procedure adalah kumpulan kode SQL yang disimpan di
database dan dapat dipanggil kembali oleh program, trigger, atau
bahkan stored procedure (rekursif)
• Stored procedure dijalankan di server, akses berikutnya akan lebih
mudah dan cepat karena SQL telah tersimpan di dalam memory.
• Stored procedure dapat menerima suatu input parameter
MEMBUAT (CREATE) STORED PROCEDURE
• syntax
CREATE PROCEDURE <schema_name.procedure_name>
(<parameter_list>
AS
SELECT <body of SELECT statement>;
• Stored procedure tidak hanya untuk pernyataan SELECT tetapi juga
dapat digunakan untuk pernyataan INSERT, UPDATE, DELETE
CREATE STORED PROCEDURE TANPA
PARAMETER
CREATE PROCEDURE
Production.Productproc
AS
SELECT productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
ORDER BY productid;
GO
CREATE STORED PROCEDURE DENGAN
PARAMETER
CREATE PROCEDURE Production.ProductsbySuppliers
(@supplierid AS INT)
AS
SELECT productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
WHERE supplierid = @supplierid
ORDER BY productid;
GO
MEMODIFIKASI (ALTER) STORED PROCEDURE
• syntax
ALTER PROCEDURE <schema_name.procedure_name>
(<parameter_list>
AS
SELECT <body of SELECT statement>;
ALTER STORED PROCEDURE
ALTER PROCEDURE
Production.ProductsbySuppliers
(@numrows AS INT, @supplierid AS INT)
AS
SELECT TOP (@numrows) productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
WHERE supplierid = @supplierid
ORDER BY productid;
GO
MENJALANKAN STORED PROCEDURE
• Stored procedure dijalankan dengan perintah EXECUTE atau EXEC
• Setelah perintah EXECUTE atau EXEC diikuti dengan nama procedure
EXEC Production.ProductsbySuppliers
• Jika procedure menerima parameter, maka setelah nama procedure
digunakan parameter dan tipe data yang bersesuaian
EXEC Production.ProductsbySuppliers
@supplierid = 1, @numrows = 2
MENJALANKAN STORED PROCEDURE
CREATE PROCEDURE Production.Productproc
AS
SELECT productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
ORDER BY productid;
GO
EXEC Production.Productproc;
MENJALANKAN STORED PROCEDURE (1)
CREATE PROCEDURE Production.ProductsbySuppliers
(@supplierid AS INT)
AS
SELECT productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
WHERE supplierid = @supplierid
ORDER BY productid;
GO
EXEC Production.ProductsbySuppliers
@supplierid = 1;
MENJALANKAN STORED PROCEDURE (2)
CREATE PROCEDURE Production.TopProductsbySuppliers
(@numrows AS INT, @supplierid AS INT)
AS
SELECT TOP (@numrows) productid,
productname,
categoryid,
unitprice,
discontinued
FROM Production.Products
WHERE supplierid = @supplierid
ORDER BY unitprice DESC;
GO
EXEC Production.TopProductsbySuppliers
@numrows = 2, @supplierid=1;
MENGHAPUS STORED PROCEDURE
• Menggunakan perintah DROP PROCEDURE
IF OBJECT_ID('Production.ProductsbySuppliers','P') IS NOT
NULL DROP PROCEDURE Production.ProductsbySuppliers;
SQL DINAMIS
• SQL dinamis memungkinkan untuk membaca karakter string, dan
mengeksekusinya sebagai perintah SQL
• Terdapat 2 metode:
q Perintah EXECUTE atau EXEC menerima string sebagai input
q Menggunakan system stored procedure, yaitu sp_executesql
SQL DINAMIS: ‘STRING’
DECLARE @sqlstring AS VARCHAR(1000);
SET @sqlstring='SELECT empid,' + ' lastname '+' FROM HR.employees;'
EXEC(@sqlstring);
GO
SQL DINAMIS: SP_EXECUTESQL
DECLARE @sqlcode AS NVARCHAR(256) = N'SELECT GETDATE() AS dt';
EXEC sys.sp_executesql @statement = @sqlcode;
GO
SQL DINAMIS ‘STRING’ VS SP_EXECUTESQL
DECLARE @sqlstring AS VARCHAR(1000);
DECLARE @empid AS INT;
SET @sqlstring='SELECT empid, lastname FROM HR.employees WHERE empid=@empid;'
EXEC(@sqlstring);
GO
SQL DINAMIS ‘STRING’ VS SP_EXECUTESQL (1)
DECLARE @sqlstring AS NVARCHAR(1000);
DECLARE @empid AS INT;
SET @sqlstring=N'SELECT empid, lastname FROM HR.employees WHERE empid=@empid;'
EXEC sys.sp_executesql @statement = @sqlstring, @params=N'@empid AS INT',
@empid = 5;
SQL DINAMIS ‘STRING’ VS SP_EXECUTESQL (2)
Kesimpulan:
• sp_executesql bisa menerima input parameter
• sp_executesql menerima string dengan tipe data nvarchar
TERIMAKASIH
