{"id":246,"date":"2014-03-09T23:42:04","date_gmt":"2014-03-09T15:42:04","guid":{"rendered":"http:\/\/suherman.asia\/w2\/?p=246"},"modified":"2014-03-09T23:56:33","modified_gmt":"2014-03-09T15:56:33","slug":"dynamic-sql","status":"publish","type":"post","link":"http:\/\/suherman.asia\/w2\/dynamic-sql.html","title":{"rendered":"Dynamic SQL"},"content":{"rendered":"<p>Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis. Maksud dinamis ini misalkan nama table yang kadang gabungan antara nama object dan tahun, atau susunan field yang diinginkan selalu berubah-ubah.<!--more--><\/p>\n<p>Kondisi tersebut diatas dapat diatasi dengan menggunakan Dynamic SQL yaitu dengan memanfaatkan statement SP_EXECUTESQL.<\/p>\n<p>Namun dalam penggunaan SP_EXECUTESQL ini harap diperhatikan sisi keamanannya, karena akan rawan sekali terjadi SQL Injection. Untuk mengeliminirnya maka saat menggunakan SP_EXECUTESQL dampingilah dengan penggunaan parameter.<\/p>\n<p>Syntax dari SP_EXECUTESQL adalah :<\/p>\n<blockquote><p><strong>EXECUTE SP_EXECUTESQL &lt;SQL&gt;, &lt;Parameter Definitions&gt;, &lt;Parameter Values&gt;<\/strong><\/p><\/blockquote>\n<p>Contohnya adalah sebagai berikut : misalkan kita memiliki query seperti berikut (saya menggunakan database adventureworks) :<\/p>\n<pre class=\"theme:ssms2012 lang:tsql decode:true\" title=\"T-SQL yang Mau Di Execute\">select [EmployeeID],LoginID from HumanResources.Employee\r\nwhere MaritalStatus='M' and Gender='F'<\/pre>\n<p>lalu untuk mengeksekusi query tersebut dengan Dynamic SQL adalah sebagai berikut :<\/p>\n<pre class=\"theme:vs2012 lang:tsql decode:true\" title=\"Penggunaan SP_EXECUTESQL\">Declare @sql nvarchar(500)='select [EmployeeID],LoginID from HumanResources.Employee\r\nwhere MaritalStatus=@MS and Gender=@G';\r\nDeclare @ParamDef nVarchar(50) = '@MS Varchar(5), @G Varchar(5)';\r\n\r\nexecute sp_executesql @sql, @ParamDef, @MS='M', @G='F';<\/pre>\n<p>Demikian semoga bermanfaat.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis. Maksud dinamis ini misalkan nama table yang kadang gabungan antara nama object dan tahun, atau susunan field yang diinginkan selalu berubah-ubah.<\/p>\n<p class=\"continue-reading-button\"> <a class=\"continue-reading-link\" href=\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\">Continue reading<i class=\"crycon-right-dir\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":150,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[66,14],"class_list":["post-246","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-dynamic-sql","tag-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.7 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Dynamic SQL - Suherman Blog<\/title>\n<meta name=\"description\" content=\"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic SQL - Suherman Blog\" \/>\n<meta property=\"og:description\" content=\"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.\" \/>\n<meta property=\"og:url\" content=\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\" \/>\n<meta property=\"og:site_name\" content=\"Suherman Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/emantin34\" \/>\n<meta property=\"article:published_time\" content=\"2014-03-09T15:42:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-03-09T15:56:33+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\" \/>\n\t<meta property=\"og:image:width\" content=\"424\" \/>\n\t<meta property=\"og:image:height\" content=\"134\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#article\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\"},\"author\":{\"name\":\"admin\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"headline\":\"Dynamic SQL\",\"datePublished\":\"2014-03-09T15:42:04+00:00\",\"dateModified\":\"2014-03-09T15:56:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\"},\"wordCount\":131,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"keywords\":[\"Dynamic SQL\",\"T-SQL\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\",\"url\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\",\"name\":\"Dynamic SQL - Suherman Blog\",\"isPartOf\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage\"},\"image\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage\"},\"thumbnailUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"datePublished\":\"2014-03-09T15:42:04+00:00\",\"dateModified\":\"2014-03-09T15:56:33+00:00\",\"description\":\"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.\",\"breadcrumb\":{\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/suherman.asia\/w2\/dynamic-sql.html\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage\",\"url\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"contentUrl\":\"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png\",\"width\":424,\"height\":134},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/suherman.asia\/w2\/dynamic-sql.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/suherman.asia\/w2\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/suherman.asia\/w2\/#website\",\"url\":\"http:\/\/suherman.asia\/w2\/\",\"name\":\"Suherman Blog\",\"description\":\"Just Another Geek Site\",\"publisher\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/suherman.asia\/w2\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"logo\":{\"@id\":\"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/\"},\"sameAs\":[\"http:\/\/suherman.asia\",\"https:\/\/www.facebook.com\/emantin34\",\"https:\/\/x.com\/emantin34\"],\"url\":\"http:\/\/suherman.asia\/w2\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dynamic SQL - Suherman Blog","description":"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/suherman.asia\/w2\/dynamic-sql.html","og_locale":"en_US","og_type":"article","og_title":"Dynamic SQL - Suherman Blog","og_description":"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.","og_url":"http:\/\/suherman.asia\/w2\/dynamic-sql.html","og_site_name":"Suherman Blog","article_publisher":"https:\/\/www.facebook.com\/emantin34","article_author":"https:\/\/www.facebook.com\/emantin34","article_published_time":"2014-03-09T15:42:04+00:00","article_modified_time":"2014-03-09T15:56:33+00:00","og_image":[{"width":424,"height":134,"url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","type":"image\/png"}],"author":"admin","twitter_misc":{"Written by":"admin","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#article","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html"},"author":{"name":"admin","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"headline":"Dynamic SQL","datePublished":"2014-03-09T15:42:04+00:00","dateModified":"2014-03-09T15:56:33+00:00","mainEntityOfPage":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html"},"wordCount":131,"commentCount":0,"publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"image":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","keywords":["Dynamic SQL","T-SQL"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/suherman.asia\/w2\/dynamic-sql.html#respond"]}]},{"@type":"WebPage","@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html","url":"http:\/\/suherman.asia\/w2\/dynamic-sql.html","name":"Dynamic SQL - Suherman Blog","isPartOf":{"@id":"http:\/\/suherman.asia\/w2\/#website"},"primaryImageOfPage":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage"},"image":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage"},"thumbnailUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","datePublished":"2014-03-09T15:42:04+00:00","dateModified":"2014-03-09T15:56:33+00:00","description":"Kadang kita dihadapi oleh situasi yang mengharuskan sebuah aplikasi dapat bekerja atau mengeksekusi SQL Statement yang bersifat dinamis.","breadcrumb":{"@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/suherman.asia\/w2\/dynamic-sql.html"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#primaryimage","url":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","contentUrl":"http:\/\/suherman.asia\/w2\/wp-content\/uploads\/2014\/01\/rollup.png","width":424,"height":134},{"@type":"BreadcrumbList","@id":"http:\/\/suherman.asia\/w2\/dynamic-sql.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/suherman.asia\/w2"},{"@type":"ListItem","position":2,"name":"Dynamic SQL"}]},{"@type":"WebSite","@id":"http:\/\/suherman.asia\/w2\/#website","url":"http:\/\/suherman.asia\/w2\/","name":"Suherman Blog","description":"Just Another Geek Site","publisher":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/suherman.asia\/w2\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/63654a129ee88012961c1a00415967dc","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eed6f889b4c2af2a8c18cb3bf63de6a4?s=96&d=retro&r=g","caption":"admin"},"logo":{"@id":"http:\/\/suherman.asia\/w2\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/suherman.asia","https:\/\/www.facebook.com\/emantin34","https:\/\/x.com\/emantin34"],"url":"http:\/\/suherman.asia\/w2\/author\/admin"}]}},"_links":{"self":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/246","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/comments?post=246"}],"version-history":[{"count":3,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/246\/revisions"}],"predecessor-version":[{"id":249,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/posts\/246\/revisions\/249"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media\/150"}],"wp:attachment":[{"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/media?parent=246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/categories?post=246"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/suherman.asia\/w2\/wp-json\/wp\/v2\/tags?post=246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}