{"id":36540,"date":"2018-11-20T16:25:44","date_gmt":"2018-11-20T10:55:44","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=36540"},"modified":"2021-03-11T17:04:07","modified_gmt":"2021-03-11T11:34:07","slug":"dynamic-sql","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/","title":{"rendered":"Dynamic SQL Tutorial &#8211; Introduction, Examples and Query"},"content":{"rendered":"<p>In this tutorial, we will look at how we use Dynamic SQL. Let us now move forward and learn more about dynamic SQL right from the basics.<\/p>\n<h3>What is Dynamic SQL?<\/h3>\n<p>Dynamic SQL is the process that we follow for programming SQL queries in such a way that the queries are built dynamically with the application operations.<\/p>\n<p>It helps us to manage big industrial applications and manage the transactions without any added overhead.<\/p>\n<p>With dynamic SQL we are free to create flexible SQL queries and the names of the variables or any other parameters are passed when the application runs.<\/p>\n<p>We can use stored procedures to create dynamic queries which can run when we desire.<\/p>\n<p>For Dynamic SQL, we use the exec keyword.<\/p>\n<p>When we use static SQL it is not altered from one execution to others, but in the case of dynamic SQL, we can alter the query in each execution.<\/p>\n<p>We should always prefer using static SQL over dynamic SQL for the following benefits of the static SQL:<\/p>\n<ul>\n<li>If a query compiles successfully it implies that the syntax is correct.<\/li>\n<li>If a query compiles successfully it verifies that all the permissions and validations are correct.<\/li>\n<li>As all the data is pre-known in static SQL the overhead charges are reduced considerably.<\/li>\n<\/ul>\n<h3>Why do we need Dynamic SQL?<\/h3>\n<p>We need to use Dynamic SQL for the following use cases:<\/p>\n<ol>\n<li>When we need to run dynamic queries on our database, mainly DML queries.<\/li>\n<li>When we need to access an object which is not in existence during the compile time.<\/li>\n<li>Whenever we need to optimize the run time of our queries.<\/li>\n<li>When we need to instantiate the created logic blocks.<\/li>\n<li>When we need to perform operations on application fed data using invoker rights.<\/li>\n<\/ol>\n<h3>How to use Dynamic SQL?<\/h3>\n<p>We need to follow the following syntax while creating and executing a dynamic SQL cycle.<\/p>\n<p><strong>Syntax:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">-- Start by declaring the Query variable and other required variables\r\nDECLARE @SQL nvarchar(1000)\r\nDECLARE @variable1 varchar(50)\r\nDECLARE @variable2 varchar(50)\r\n\r\n-- Set the values of the declared variables if required\r\nSET  @variable1 = 'A'\r\n\r\n-- Define the query variable\r\nSET @SQL = 'SELECT columnName1, columnName2, columnName3... \r\nFROM tableName where columnName1 = @variable1\r\n\r\n -- Prepare the statement to be run on the database\r\nPREPARE Query FROM @SQL;\r\n\r\n-- Execute the prepared  Dynamic SQL statement\r\nExecute Query;\r\n<\/pre>\n<p><strong>Example:<\/strong> Let us now see a Dynamic SQL in working condition by defining the same on our DataFlair database.<\/p>\n<p><strong>Query:<\/strong><\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\">Use DataFlair;\r\n\r\n-- Set the value of user-defined variables\r\nSET @id = 'A01';\r\n\r\n-- set the query you want to execute on the database\r\nSET @query = 'SELECT * FROM DataFlair where emp_id = @id';\r\n\r\n-- Prepare the statement to be run on the database\r\nPREPARE stmt FROM @query;\r\n\r\n-- Execute the prepared statement\r\nExecute stmt;\r\n\r\n<\/pre>\n<p><strong>Output:<\/strong><\/p>\n<p><a href=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-86936\" src=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example.png\" alt=\"Dynamic SQL Example\" width=\"1920\" height=\"1032\" srcset=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example.png 1920w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-300x161.png 300w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-1024x550.png 1024w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-150x81.png 150w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-768x413.png 768w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-1536x826.png 1536w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-720x387.png 720w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-520x280.png 520w, https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/example-320x172.png 320w\" sizes=\"auto, (max-width: 1920px) 100vw, 1920px\" \/><\/a><\/p>\n<h3>Summary<\/h3>\n<p>In this tutorial, we have seen all about Dynamic SQL. Dynamic SQL is a programming methodology that allows us to create instantaneous queries for our application.<\/p>\n<p>We have seen what are the use cases where we need to use dynamic SQL technique. After this, we have seen how to use dynamic SQL, and we have also seen the advantages and disadvantages of dynamic SQL.<\/p>\n<p>We have realized that we should avoid using dynamic SQL until not necessary as it can lead to unwanted alterations in data that are difficult to track.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this tutorial, we will look at how we use Dynamic SQL. Let us now move forward and learn more about dynamic SQL right from the basics. What is Dynamic SQL? Dynamic SQL is&#46;&#46;&#46;<\/p>\n","protected":false},"author":6,"featured_media":86935,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[16747,16746,16750,16749,16751,16748],"class_list":["post-36540","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-dynamic-query-in-sql","tag-dynamic-sql","tag-dynamic-sql-examples","tag-introduction-to-dynamic-sql","tag-use-dynamic-sql","tag-what-is-dynamic-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair<\/title>\n<meta name=\"description\" content=\"dynamic sql,dynamic query in sql,what is Dynamic SQL,Dynamic SQL Examples,use Dynamic SQL,Dynamic Structured Query Language\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/dynamic-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair\" \/>\n<meta property=\"og:description\" content=\"dynamic sql,dynamic query in sql,what is Dynamic SQL,Dynamic SQL Examples,use Dynamic SQL,Dynamic Structured Query Language\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/dynamic-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-11-20T10:55:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-11T11:34:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair","description":"dynamic sql,dynamic query in sql,what is Dynamic SQL,Dynamic SQL Examples,use Dynamic SQL,Dynamic Structured Query Language","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":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair","og_description":"dynamic sql,dynamic query in sql,what is Dynamic SQL,Dynamic SQL Examples,use Dynamic SQL,Dynamic Structured Query Language","og_url":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-11-20T10:55:44+00:00","article_modified_time":"2021-03-11T11:34:07+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"Dynamic SQL Tutorial &#8211; Introduction, Examples and Query","datePublished":"2018-11-20T10:55:44+00:00","dateModified":"2021-03-11T11:34:07+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/"},"wordCount":433,"commentCount":2,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg","keywords":["dynamic query in sql","dynamic sql","Dynamic SQL Examples","introduction to Dynamic SQL","use Dynamic SQL","what is Dynamic SQL"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/dynamic-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/","url":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/","name":"Dynamic SQL Tutorial - Introduction, Examples and Query - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg","datePublished":"2018-11-20T10:55:44+00:00","dateModified":"2021-03-11T11:34:07+00:00","description":"dynamic sql,dynamic query in sql,what is Dynamic SQL,Dynamic SQL Examples,use Dynamic SQL,Dynamic Structured Query Language","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/dynamic-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/11\/Dynamic-SQL.jpg","width":1200,"height":628,"caption":"Dynamic SQL"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/dynamic-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SQL Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"Dynamic SQL Tutorial &#8211; Introduction, Examples and Query"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36540","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=36540"}],"version-history":[{"count":6,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36540\/revisions"}],"predecessor-version":[{"id":86937,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/36540\/revisions\/86937"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86935"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=36540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=36540"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=36540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}