Belajar sp_executesql di SQL Server

sp_executesql merupakan store procedure di SQL Server yang fungsinya untuk mengeksekusi kalimat SQL yang dibuat oleh user dan bisa bersifat dinamis. Informasi lengkap tentang sp_executesql bisa diakses di MSDN. Berikut adalah source code yang saya buat untuk mempelajari sp_executesql ini. Source code ini dibuat dengan menggunakan SQL Server Management Studio untuk SQL Server 2014. Database yang digunakan adalah AdventureWorks2014. Bagi yang belum memiliki databasenya, silahkan baca artikel ini untuk mengetahui link download dan cara installnya.

 -- JUDUL : BELAJAR PROSEDURE sp_executesql
-- =======================================

-- CASE 1 : sp_executesql SEDERHANA [RESULT = 31.465 ROWS]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200); -- BUAT VARIABEL
SET @SQLSelect = N'SELECT * FROM [Sales].[SalesOrderHeader];'; -- ISI NILAI VARIABEL
EXECUTE sp_executesql @SQLSelect; -- JALANKAN sp_executesql
GO

-- CASE 2 : sp_executesql DENGAN 1 PARAMETER [RESULT = 486 ROWS]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @KumpulanNamaParameter NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT;
SET @SQLSelect = N'SELECT * FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID;';
SET @KumpulanNamaParameter = N'@NilaiTerritoryID SMALLINT';
SET @NilaiParameterTerritoryID = 5;
EXECUTE sp_executesql @SQLSelect, @KumpulanNamaParameter, @NilaiTerritoryID=@NilaiParameterTerritoryID;
GO

-- CASE 3a : sp_executesql DENGAN 2 PARAMETER (PENULISAN DENGAN VARIABLE @KumpulanNamaParamater) [RESULT = 14 ROWS]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @KumpulanNamaParameter NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT;
DECLARE @NilaiParameterOrderDate DATE;
SET @SQLSelect = N'SELECT * FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @KumpulanNamaParameter = N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, @KumpulanNamaParameter, @NilaiTerritoryID=@NilaiParameterTerritoryID, @NilaiOrderDate=@NilaiParameterOrderDate;
GO

-- CASE 3b : sp_executesql DENGAN 2 PARAMETER (PENULISAN TANPA VARIABLE @KumpulanNamaParamater) [RESULT = 14 ROWS]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT;
DECLARE @NilaiParameterOrderDate DATE;
SET @SQLSelect = N'SELECT * FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE', @NilaiParameterTerritoryID, @NilaiParameterOrderDate;
GO

-- CASE 4a : sp_executesql DENGAN 2 PARAMETER INPUT & 1 PARAMETER OUTPUT (TEKNIK PENULISAN 1) [RESULT = 1 ROWS DENGAN NILAI 14]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @KumpulanNamaParameter NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT;
DECLARE @NilaiParameterOrderDate DATE;
DECLARE @OutputBanyakData INT;
SET @SQLSelect = N'SELECT @OutputBanyakDataOUT = COUNT([SalesOrderID]) FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @KumpulanNamaParameter = N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE, @OutputBanyakDataOUT INT OUTPUT';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, @KumpulanNamaParameter, @NilaiTerritoryID=@NilaiParameterTerritoryID, @NilaiOrderDate=@NilaiParameterOrderDate, @OutputBanyakDataOUT=@OutputBanyakData OUTPUT;
SELECT @OutputBanyakData AS [BanyakData];
GO

-- CASE 4b : sp_executesql DENGAN 2 PARAMETER INPUT & 1 PARAMETER OUTPUT (TEKNIK PENULISAN 2) [RESULT = 1 ROWS DENGAN NILAI 14]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT;
DECLARE @NilaiParameterOrderDate DATE;
DECLARE @OutputBanyakData INT;
SET @SQLSelect = N'SELECT @OutputBanyakDataOUT = COUNT([SalesOrderID]) FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE, @OutputBanyakDataOUT INT OUTPUT', @NilaiParameterTerritoryID, @NilaiParameterOrderDate, @OutputBanyakData OUTPUT;
SELECT @OutputBanyakData AS [BanyakData];
GO

-- CASE 5a : sp_executesql DENGAN 2 PARAMETER INPUT & 2 PARAMETER OUTPUT (TEKNIK PENULISAN 1) [RESULT = 1 ROWS DENGAN NILAI 14 & 303545.8169]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @KumpulanNamaParameter NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT, @NilaiParameterOrderDate DATE;
DECLARE @OutputBanyakData INT, @OutputTotalDue MONEY;
SET @SQLSelect = N'SELECT @OutputBanyakDataOUT = COUNT([SalesOrderID]), @OutputTotalDueOUT = SUM([TotalDue]) FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @KumpulanNamaParameter = N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE, @OutputBanyakDataOUT INT OUTPUT, @OutputTotalDueOUT MONEY OUTPUT';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, @KumpulanNamaParameter, @NilaiTerritoryID=@NilaiParameterTerritoryID, @NilaiOrderDate=@NilaiParameterOrderDate, @OutputBanyakDataOUT=@OutputBanyakData OUTPUT, @OutputTotalDueOUT=@OutputTotalDue OUTPUT;
SELECT @OutputBanyakData AS [BanyakData], @OutputTotalDue AS [TotalData];
GO

--CASE 5b : sp_executesql DENGAN 2 PARAMETER INPUT & 2 PARAMETER OUTPUT (TEKNIK PENULISAN 2) [RESULT = 1 ROWS DENGAN NILAI 14 & 303545.8169]
USE [AdventureWorks2014];
GO
DECLARE @SQLSelect NVARCHAR(200);
DECLARE @NilaiParameterTerritoryID SMALLINT, @NilaiParameterOrderDate DATE;
DECLARE @OutputBanyakData INT, @OutputTotalDue MONEY;
SET @SQLSelect = N'SELECT @OutputBanyakDataOUT = COUNT([SalesOrderID]), @OutputTotalDueOUT = SUM([TotalDue]) FROM [Sales].[SalesOrderHeader] WHERE [TerritoryID]=@NilaiTerritoryID AND [OrderDate]=@NilaiOrderDate;';
SET @NilaiParameterTerritoryID = 5;
SET @NilaiParameterOrderDate = '2012-05-01';
EXECUTE sp_executesql @SQLSelect, N'@NilaiTerritoryID SMALLINT, @NilaiOrderDate DATE, @OutputBanyakDataOUT INT OUTPUT, @OutputTotalDueOUT MONEY OUTPUT', @NilaiParameterTerritoryID, @NilaiParameterOrderDate,@OutputBanyakData OUTPUT, @OutputTotalDue OUTPUT;
SELECT @OutputBanyakData AS [BanyakData], @OutputTotalDue AS [TotalData];
GO