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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s