{"id":75827,"date":"2023-06-25T11:32:35","date_gmt":"2023-06-25T15:32:35","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/ms-sql-server-dynamic-sql-t-sql\/"},"modified":"2023-06-25T11:32:35","modified_gmt":"2023-06-25T15:32:35","slug":"ms-sql-server-dynamic-sql-t-sql","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=75827","title":{"rendered":"MS SQL Server Dynamic SQl, T-SQL"},"content":{"rendered":"<h1> <strong>MS SQL Server<\/strong><br \/>\n<strong>Dynamic SQl, T-SQL<\/strong><\/h1>\n<ul>\n<li>\n<h2>Mostly:<\/h2>\n<\/li>\n<li>\n<h3>Dynamic SQL<\/h3>\n<\/li>\n<li>\n<h3>Stored Procedure<\/h3>\n<\/li>\n<li>\n<h3>Trigger<\/h3>\n<\/li>\n<li>\n<h3>Cursor<\/h3>\n<\/li>\n<li>\n<h3>Function<\/h3>\n<\/li>\n<li>\n<h2>Sayed Ahmed<\/h2>\n<\/li>\n<\/ul>\n<h1> What are the Most Important<br \/>\nMost Used<\/h1>\n<ul>\n<li>\n<h2>Design ERD<\/h2>\n<\/li>\n<li>\n<h3>Convert ERD to database<\/h3>\n<\/li>\n<li>\n<h2>Normalization<\/h2>\n<\/li>\n<li>\n<h2>Indexing<\/h2>\n<\/li>\n<li>\n<h2>SQL<\/h2>\n<\/li>\n<li>\n<h3>Stored Procedure<\/h3>\n<\/li>\n<li>\n<h3>Dynamic SQL<\/h3>\n<\/li>\n<li>\n<h3>These will come, not too frequent<\/h3>\n<\/li>\n<li>\n<h4>Function, User Defined Data Types, Temporary Table<\/h4>\n<\/li>\n<li>\n<h4>Trigger, Cursor<\/h4>\n<\/li>\n<li>\n<h2>SSRS, SSIS, SSAS<\/h2>\n<\/li>\n<li>\n<h3>ML in SQL Server (in advanced level)<\/h3>\n<\/li>\n<li>\n<h4>May not be important initially<\/h4>\n<\/li>\n<\/ul>\n<h2>\n<\/h2>\n<h1> Today<\/h1>\n<ul>\n<li>\n<h2>More on<\/h2>\n<\/li>\n<li>\n<h3>Dynamic SQL<\/h3>\n<\/li>\n<li>\n<h3>Stored Procedure<\/h3>\n<\/li>\n<li>\n<h3>Cursor<\/h3>\n<\/li>\n<\/ul>\n<h3>\n<\/h3>\n<h1> Basics of Stored Procedure<\/h1>\n<ul>\n<li>\n<h2>By this time, we are supposed to know<\/h2>\n<\/li>\n<li>\n<h3><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/basic-sql-server-stored-procedures\/\">https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/basic-sql-server-stored-procedures\/<\/a><\/h3>\n<\/li>\n<li>\n<h3><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-stored-procedure-parameters\/\">https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-stored-procedure-parameters\/<\/a> <\/h3>\n<\/li>\n<li>\n<h3><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/variables\/\">https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/variables\/<\/a> <\/h3>\n<\/li>\n<li>\n<h2>Although these topics will come back<\/h2>\n<\/li>\n<li>\n<h3>In our discussion today<\/h3>\n<\/li>\n<\/ul>\n<h2>\n<\/h2>\n<h1> From Northwind Database<\/h1>\n<ul>\n<li>\n<h2>Write a dynamic SQL <\/h2>\n<\/li>\n<li>\n<h3>That will Query the Products table and <\/h3>\n<\/li>\n<li>\n<h4>Show all data<\/h4>\n<\/li>\n<li>\n<h2>Ref:<\/h2>\n<\/li>\n<li>\n<h3><a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-dynamic-sql\/\">https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-dynamic-sql\/<\/a><\/h3>\n<\/li>\n<\/ul>\n<h3>\n<\/h3>\n<h1> Dynamic SQL<\/h1>\n<ul>\n<li>\n<h2>DECLARE\u2006 \u2006 @table NVARCHAR(128), @sql NVARCHAR(MAX);<\/h2>\n<\/li>\n<li>\n<h2>SET @table = N&#8217;Products&#8217;<\/h2>\n<\/li>\n<li>\n<h2>SET @sql = N&#8217;SELECT * FROM &#8216; + @table;<\/h2>\n<\/li>\n<li>\n<h2>EXEC sp_executesql @sql;<\/h2>\n<\/li>\n<\/ul>\n<h1> Convert the previous to a stored procedure<\/h1>\n<h1> Using dynamic SQL to query from any table\u2006 \u2006 of Northwind Database<\/p>\n<p>Write a stored Procedure for it<br \/>\ntable name as the parameter<\/h1>\n<h1>Create or ALTER\u2006 \u2006 \u2006 Procedure\u2006 \u2006 [dbo].[usp_query] ( @table NVARCHAR(128))<br \/>\nAS<br \/>\nBEGIN<\/p>\n<\/h1>\n","protected":false},"excerpt":{"rendered":"<p>MS SQL Server Dynamic SQl, T-SQL Mostly: Dynamic SQL Stored Procedure Trigger Cursor Function Sayed Ahmed What are the Most Important Most Used Design ERD Convert ERD to database Normalization Indexing SQL Stored Procedure Dynamic SQL These will come, not too frequent Function, User Defined Data Types, Temporary Table Trigger, Cursor SSRS, SSIS, SSAS ML &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"http:\/\/bangla.sitestree.com\/?p=75827\">Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[182],"tags":[],"class_list":["post-75827","post","type-post","status-publish","format-standard","hentry","category---blog","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":75975,"url":"http:\/\/bangla.sitestree.com\/?p=75975","url_meta":{"origin":75827,"position":0},"title":"SQL Server: Dynamic SQL, Stored Procedure, Cursor, SQL Injection, and similar","author":"Sayed","date":"April 27, 2024","format":false,"excerpt":"SQL Server: Dynamic SQL, Stored Procedure, Cursor, SQL Injection, and similar https:\/\/youtu.be\/uje72uNAT6I?list=PLUA7SYgJYDFoharKbJxz2Hxw6xQITXvBn","rel":"","context":"In &quot;\u09ac\u09cd\u09b2\u0997 \u0964 Blog&quot;","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":76602,"url":"http:\/\/bangla.sitestree.com\/?p=76602","url_meta":{"origin":75827,"position":1},"title":"Dynamic SQL","author":"Sayed","date":"January 30, 2025","format":false,"excerpt":"\"Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed\" https:\/\/www.sqlshack.com\/dynamic-sql-in-sql-server\/ Example: ChatGPT DECLARE @sql AS NVARCHAR(MAX) SET @sql = 'SELECT * FROM Employees WHERE Department = ''' + @department + '''' EXEC sp_executesql @sql Another Example: Oracle table_name := 'HR.Employees'; dyn_sql\u2026","rel":"","context":"In &quot;Root&quot;","block_context":{"text":"Root","link":"http:\/\/bangla.sitestree.com\/?cat=1"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":75826,"url":"http:\/\/bangla.sitestree.com\/?p=75826","url_meta":{"origin":75827,"position":2},"title":"Some Topics to Know and Understand to prepare for a DBMS related Job Interview","author":"Sayed","date":"June 25, 2023","format":false,"excerpt":"Some Topics to Know and Understand to prepare for a DBMS related Job Interview ACID ACID: https:\/\/www.geeksforgeeks.org\/acid-properties-in-dbms Database Normalization 1NF: No repeating groups possible for a cell, PK identified, dependencies mapped 2NF: No Partial Dependence. Non key attributes must have to depend on the full key 3NF: No transitive dependency.\u2026","rel":"","context":"In &quot;\u09ac\u09cd\u09b2\u0997 \u0964 Blog&quot;","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":69249,"url":"http:\/\/bangla.sitestree.com\/?p=69249","url_meta":{"origin":75827,"position":3},"title":"What are cursors? #38","author":"Author-Check- Article-or-Video","date":"August 15, 2021","format":false,"excerpt":"Cursors are server side database objects that are used by applications to apply operations on the database table data on a row-by-row basis. The operations may vary from one row to another row dynamically based on the requirements (business logic) and also multiple operations can be performed on the same\u2026","rel":"","context":"In &quot;FromSitesTree.com&quot;","block_context":{"text":"FromSitesTree.com","link":"http:\/\/bangla.sitestree.com\/?cat=1917"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":78165,"url":"http:\/\/bangla.sitestree.com\/?p=78165","url_meta":{"origin":75827,"position":4},"title":"Oracle PL\/SQL Concepts","author":"Sayed","date":"May 14, 2025","format":false,"excerpt":"Oracle PL\/SQL Concepts BLOCK declare .... Begin...End. Cursor Trigger Programming Clauses: if..then...else, case when, loops (for, while) Stored Procedure Function Advanced SQLs and Analytics Functions GROUP BY ROLLUP(), GROUP BY CUBE() RANK(), DENSE_RANK(), ROW_NUMBER() PARTITION_BY, ORDER BY X NULLS Last, ORDER BY X NULLS FIRST, RANGE BETWEEN INTERVAL 30 DAY\u2026","rel":"","context":"In &quot;Root&quot;","block_context":{"text":"Root","link":"http:\/\/bangla.sitestree.com\/?cat=1"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":76598,"url":"http:\/\/bangla.sitestree.com\/?p=76598","url_meta":{"origin":75827,"position":5},"title":"What is a Dynamic Cursor in Oracle PL\/SQL","author":"Sayed","date":"January 29, 2025","format":false,"excerpt":"Dynamic\/Ref Cursor CREATE OR REPLACE PROCEDURE query_invoice( month VARCHAR2, year VARCHAR2) IS TYPE cur_typ IS REF CURSOR; c cur_typ; query_str VARCHAR2(200); inv_num NUMBER; inv_cust VARCHAR2(20); inv_amt NUMBER;BEGIN query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year || ' WHERE invnum = :id'; OPEN c FOR query_str USING\u2026","rel":"","context":"In &quot;Root&quot;","block_context":{"text":"Root","link":"http:\/\/bangla.sitestree.com\/?cat=1"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bangla.sitestree.com\/wp-content\/uploads\/2025\/01\/image-20.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/75827","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=75827"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/75827\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=75827"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=75827"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=75827"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}