💡An interesting advantage of using dynamic SQL is that it provides access to any database because the dynamic code can append the database name before an object’s name.
As we know that purpose of dynamic query also does matter because by using them we can combine one or more T-SQL statements. These combinations may be based on conditional data joins, conditional where clause or condition output columns.
👁️EXEC() – This feature was introduced in SQL 6.0
and mostly used for quick throw-away things and DBA tasks. We can use 2000 characters
query string to invoke this command. This is the one parameter based SQL
command. It is less hassle than sp_executesql and easily permits us to
make very quick things.
---- Create data table Employee
CREATE TABLE
Employee
(
EmpId INT,
EmpName VARCHAR(25),
Department VARCHAR(25)
)
---- Insert Data into Employee
INSERT INTO
Employee (EmpId,EmpName,Department)
VALUES (1, 'Ryan
Arjun','Finance'),
(2, 'Kimmy Wang','Admin'),
(3, 'Lucy Gray','Sales'),
(4, 'Billy Doug','Admin'),
(5, 'Gery Dean','IT')
----Pull the data from Employee
SELECT EmpId,EmpName,Department
FROM DBO.Employee
|
❓How
to use Dynamic SQL
By using the above table,
we can create the dynamic SQL by declaring three variables such as first
variable to define the requested columns, second variable to define the source
table name and third variable to create the dynamic SQL query as given below-
---- Declare local Variable
DECLARE @inpColumns VARCHAR(30),
@inpTableName VARCHAR(30),
@SQL NVARCHAR(MAX);
---- Set values
SET @inpColumns =
'EmpId,EmpName,Department';
SET @inpTableName =
'Employee';
---- Create Dynamic SQL
SET @SQL = 'SELECT '+@inpColumns+' FROM dbo.' + @inpTableName;
---- PRINT Dynamic SQL
PRINT @SQL
---- Invoke Dynamic SQL
EXEC(@SQL)
---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
|
❓How To Build A Dynamic Pivot Table?
If we want to get
department wise employees and show them into column wise instead of rows then
we can use dynamic SQL. We can get the dynamic column name for each department.
We need to use two variables; one variable to hold the dynamic columns and
another variable to store dynamic SQL. In this example, we are using pivot
feature of SQL in dynamic SQL such as –
---- Declare local Variable
DECLARE @inpPivotColumns VARCHAR(200)='',
@SQL
NVARCHAR(MAX);
----- Set Columns Values for dynamic columns SET @inpPivotColumns = (SELECT N', ' + STRING_AGG(QUOTENAME(DEPARTMENT),',')
FROM (SELECT DISTINCT DEPARTMENT FROM
DBO.EMPLOYEE)XYZ);
------ Create Dynamic SQL
SET @SQL = 'SELECT '+STUFF(@inpPivotColumns, 1, 2, '')+' FROM
(SELECT DEPARTMENT, COUNT(*) as CTR
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENT
)TBL
PIVOT
( SUM(CTR) FOR DEPARTMENT IN ('
+ STUFF(REPLACE(@inpPivotColumns, ', DEPT.[', ',['), 1, 1, '')
+ ')
) AS DEPT';
------ PRINT Dynamic SQL
PRINT @SQL
---- Invoke Dynamic SQL
EXEC(@SQL)
---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
|
🚀Benefits of Dynamic SQL
👏Easily translates the input data, including any parameter markers, into an SQL statement.👏Easily provide a way to prepares the SQL statement to execute and acquires a description of the result table.👏Provide a simple way to reusable code for database objects and integrate variables, parameters and joins.👏Provide a sorting functionality for any requested data column.👏Easily implement the conditional data filter as well as data output also.👏Obtains, for SELECT statements, enough main storage to contain retrieved data and executes the statement or fetches the rows of data👏Processes the information returned and handles SQL return codes.
If there are a lots of benefits of dynamic SQL scrip but they have some drawbacks also as given below:
1 - Writing dynamic queries inside the procedure is very near to SQL Injection attacks.
2 - When a stored procedure is executed first time, SQL Server caches the execution plan, and served the next request from this cache. This gives much performance difference. But dynamic queries won't allow to generate a static execution plan, and this will be compiled for every request.
3 - We need to be vary careful to write them to avoid the syntax errors because SQL Server does not show the syntax errors intelligence.
4 - It's very tough to debug the queries and spent a lot of time to debug them.
5 - It hides errors in queries since it is appended in a string.
6 - We cannot use them in SQL functions and views.
6 - We cannot use them in SQL functions and views.
Hope you enjoyed and found this essay useful. Please leave your questions and suggestions in the comments section. Many thanks for reading! 😊
Your support is greatly appreciated! If you found this article valuable, don’t forget to clap👏, follow✌️, and subscribe❤️💬🔔 to stay connected and receive more insightful content. Let’s grow and learn together!
⭐To learn more, please follow us —
http://www.sql-datatools.com
⭐To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
⭐To Learn more, please visit our Instagram account at —
https://www.instagram.com/asp.mukesh/
⭐To Learn more, please visit our twitter account at —
https://twitter.com/macxima
⭐To Learn more, please visit our Medium account at —
https://medium.com/@macxima
http://www.sql-datatools.com
⭐To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
⭐To Learn more, please visit our Instagram account at —
https://www.instagram.com/asp.mukesh/
⭐To Learn more, please visit our twitter account at —
https://twitter.com/macxima
⭐To Learn more, please visit our Medium account at —
https://medium.com/@macxima
No comments:
Post a Comment