Query Dinamis SQL Server

Perintah EXECUTE pada SQL Server digunakan untuk menjalankan procedure, function dan bisa juga string yang berisi perintah SQL. Tanda kurung buka dan kurung tutup mutlak digunakan sebelum dan sesudah string perintah SQL. Misalnya untuk menjalankan procedure bisa seperti ini:
EXECUTE (‘sp_databases’);
EXECUTE (‘SELECT * FROM sysdatabases’);

Jika kita memerlukan query yang menghasilkan jumlah field yang bisa berbeda, cara ini bisa menjadi solusi. Misalnya menampilkan hasil penjualan per tahun dari perusahaan. Tidak menjadi masalah jika tahun ditampilkan secara vertikal, tapi jika ditampilkan secara horisontal tentu tidak semudah itu. Disini diberikan contoh kasus dengan 3 tabel, periode, cabang dan penjualan. Tabel periode berisi tahun penjualan, tabel cabang berisi cabang perusahaan, dan tabel penjualan adalah data penjualan berdasarkan tanggal pada suatu cabang. Tabel-tabel tersebut di desain seperti ini.
CREATE TABLE periode(tahun int);
INSERT INTO periode VALUES (2007);
INSERT INTO periode VALUES (2008);
INSERT INTO periode VALUES (2009);

CREATE TABLE cabang(kode char(2));
INSERT INTO cabang VALUES ('01');
INSERT INTO cabang VALUES ('02');

CREATE TABLE penjualan(kode char(5), tanggal datetime, total money, kodecabang char(2));
INSERT INTO penjualan VALUES ('00001', '2007-02-21', 80000, '01');
INSERT INTO penjualan VALUES ('00002', '2007-07-09', 35000, '02');
INSERT INTO penjualan VALUES ('00003', '2008-01-11', 20000, '01');
INSERT INTO penjualan VALUES ('00004', '2008-04-15', 63000, '01');
INSERT INTO penjualan VALUES ('00005', '2008-12-30', 48000, '02');
INSERT INTO penjualan VALUES ('00006', '2009-03-03', 50000, '01');

Yang kita inginkan adalah menampilkan total penjualan per tahun per cabang dengan tahun ditampilkan secara horisontal. Dengan bantuan cursor hasil yang diinginkan bisa kita dapatkan.
DECLARE @tahun char(4);
DECLARE @query varchar(1000);
SET @query = 'SELECT c.kode AS cabang, ';
DECLARE crs CURSOR LOCAL FOR SELECT tahun FROM periode;
OPEN crs;
FETCH NEXT FROM crs INTO @tahun
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @query = @query + 'SUM(CASE WHEN YEAR(p.tanggal) = ' + @tahun + ' THEN p.total ELSE 0 END)' + ' AS tahun' + @tahun;
  FETCH NEXT FROM crs INTO @tahun;
  IF @@FETCH_STATUS = 0 SET @query = @query + ', '
END
CLOSE crs
DEALLOCATE crs
SET @query = @query + ' FROM cabang AS c LEFT JOIN penjualan AS p ON p.kodecabang = c.kode GROUP BY c.kode';
EXECUTE (@query);

Hasilnya dari skrip diatas adalah sebagai berikut.





Untuk mendapatkan hasil dari perintah SQL seperti diatas tidak bisa didapatkan seperti mengakses tabel biasa. Jadi perintah berikut akan menghasilkan pesan kesalahan.
SELECT * FROM EXECUTE (‘SELECT * FROM sysdatabases’);

Caranya adalah menyimpan hasil dari perintah SQL tersebut ke suatu tabel atau tabel sementara. Contoh berikut akan menyimpan hasil dari beberapa perintah SQL ke tabel sementara dan menampilkan hasilnya.
INSERT INTO #tmp EXECUTE ('prosedur1');
INSERT INTO #tmp EXECUTE ('prosedur2');
INSERT INTO #tmp EXECUTE ('prosedur3');
SELECT * FROM #tmp;