Dynamic SQL for Dynamic Sorting
Dynamic sorting using dynamic SQL in SQL Server is a method to sort one or more columns of a dynamic query result set dynamically and flexibly. By this method, the user can determine the sorting order of the result set at runtime instead of being hardcoded in the query.
Example of Dynamic Sorting
Create Procedure spSortStudentData
@strOrderBy nvarchar(30)
As
Begin
Declare @SqlQuery nvarchar(1000)
Set @SqlQuery= 'Select * from Students Order By ' + @strOrderBy
Exec(@SqlQuery)
End
This stored procedure can be run using the below execution command:
Exec spSortStudentData 'Student_Name'
Output:
Explanation: In the above example the Student table data can be displayed by passing the column name by which the user wants to ‘Sort‘ the data, and as an example the table data is sort by ‘Student_Name‘ by sending this column name value when the stored procedure ‘spSortStudentData‘ is executed.
Dynamic SQL in SQL Server
In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures. In this article let us look into the details about how to create a dynamic SQL and its uses and also what are the security issues that may arise and how to handle those security issues.