Dynamic SQL Statement in SQL Server
Dynamic SQL:
A dynamic sql statement is a set of sql statements which are constructed at execution time.
We may have to face certain condition in our application development where we might have to retrieve records from different table based on different conditions then in that scenario we do use dynamic SQL.
These dynamic Sql statement doesn’t parsed at compile time so it may introduce security vulnerabilities in our databse so we should try to avoid using dynamic sql as much as possible.
There are two ways to execute a dynamic sql statement in sql server:-
sp_executesql
EXECUTE()
Although these two methods produces same results but there might be certain scenario where it may produce different results.
Following is little description about the above two methods:
1. sp_executesql :-
It is a system stored procedure.
It allows parameters to be passed IN or OUT of the dynamic sql statement.
It is less susceptible to SQL Injection.
Higher chance for sql string to remain in cache which results better performance when the same sql statement is executed.
Clean code hence easier to read and maintain.
Support parameter substitution hence more preferable than EXECUTE command.
Syntax:-
sp_executesql [@sqlstmt ],[ @ParameterDefinitionList],[ @ParameterValueList ]
2. EXECUTE():-
When we use this command the parameters should be converted to characters.
Syntax:-
EXECUTE (@sqlStmt)
Example:-
Create procedure sp_GetSalesHistory
(
@WhereClouse nvarchar(2000)=NULL,
@TotalRowsReturned INT OPUTPUT
)
AS
BEGIN
DECLARE @SelectStmt nvarchar(),
DECLARE @FullStmt nvarchar(),
DECLARE @ParameterList nvarchar()
SET @ ParameterList = ’@TotalRowsReturned INT OUTPUT ’
SET @ SelectStmt = ‘SELECT @ TotalRowsReturned = COUNT(*) FROM SalesHistory’
SET @ FullStmt = @ SelectStmt + ISNULL(@WhereClouse,’ ’)
PRINT @ FullStmt
EXECUTE sp_executesql @ FullStmt ,@ ParameterList ,@ TotalRowsReturned =@ TotalRowsReturned OUTPUT
END
