Friday, February 12, 2016

SQL - Dynamic SQL Scripts

Sometimes, it's become urgent to use the dynamic columns, dynamic data filter parameters or dealing with dynamic tables and views names to meet the requirements then Dynamic SQL queries come to the picture to generate the expected outputs and writing dynamic SQL queries is become a very urgent task that requires much more discipline and understanding to avoid losing control over the SQL code. Dynamic SQL query can become very messy, and be difficult to read, troubleshoot and for the maintenance also. 
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.
Dynamic SQL query is dependent on one of the following SQL commands - 
  1. Sp_ExecuteSql – This feature was introduced in SQL Server 7 which is a built-in stored procedure that takes two pre-defined parameters and any number of user-defined parameters. This is mostly usable command to invoke the dynamic SQL because it will prevail. We can use 4000 characters query string to invoke this command. The first parameter @inpStatement is mandatory, and contains a batch of one or more SQL statements and second parameter @inpParameters is optional and they are exactly the same as for the parameter list of a stored procedure. To prevent from SQL injections, this is the best option to use dynamic SQL. 
  2. 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.
To understand the Dynamic SQL, we will use the following table structure as given below-
---- 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
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
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

SELECT EmpId,EmpName,Department FROM dbo.Employee


---- Invoke Dynamic SQL
EXEC(@SQL)
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT

---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT

Dynamic Pivot Query
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 values
SELECT @inpPivotColumns += N', ' + 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

SELECT [Admin], [Finance], [IT], [Sales] FROM
(SELECT DEPARTMENT, COUNT(*) as CTR
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENT
)TBL
PIVOT
( SUM(CTR) FOR DEPARTMENT IN ( [Admin], [Finance], [IT], [Sales])
) AS DEPT


---- Invoke Dynamic SQL
EXEC(@SQL)
Admin
Finance
IT
Sales
2
1
1
1

---- Invoke Dynamic SQL
EXEC sp_executesql @SQL
Admin
Finance
IT
Sales
2
1
1
1


Benefits of Dynamic SQL
There are a lots of benefits of dynamic SQL scripts. For an example, we are building a dynamic web page or web report to accept the conditional based multiple parameters to generate the output result then dynamic SQL script approach is the best way to handle this kind of requirement because of Dynamic SQL have the following benefits- 
  1. Easily translates the input data, including any parameter markers, into an SQL statement.
  2. Easily provide a way to prepares the SQL statement to execute and acquires a description of the result table. 
  3. Provide a simple way to reusable code for database objects and integrate variables, parameters and joins.
  4. Provide a sorting functionality for any requested data column.
  5. Easily implement the conditional data filter as well as data output also.
  6. Obtains, for SELECT statements, enough main storage to contain retrieved data and executes the statement or fetches the rows of data
  7. Processes the information returned and handles SQL return codes.
Drawbacks  of Dynamic SQL
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.

No comments:

Post a Comment

Popular Posts