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 -
- 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.
- 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
|
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
---- 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.
No comments:
Post a Comment