Write a Stored Procedure that takes column name as a parameter and returns the result sorted by the column that is passed

Let's understand the requirement better with an example. I have an Employee table as shown below.



I want a stored procedure that returns employee data sorted by a column, that the user is going to pass into the stored procedure as a parameter. There are 2 ways of doing this.

Option 1: Use Case Statement as shown below:
Create Proc spGetEmployeesSorted
@SortCoumn nvarchar(10)
as
Begin

Select [Id],[Name],[Gender],[Salary],[City]
From   [Employee]
Order by Case When @SortCoumn = 'Id' Then Id End,
                Case When @SortCoumn = 'Name' Then Name End,
                Case When @SortCoumn = 'Gender' Then Gender End,
                Case When @SortCoumn = 'Salary' Then Salary End,
                Case When @SortCoumn = 'City' Then City End

End


Option 2: Use Dynamic SQL as shown below:
Create Proc spGetEmployeesSortedUsingDynamicSQL
@SortCoumn nvarchar(10)
as
Begin

Declare @DynamicQuery nvarchar(100)
Set @DynamicQuery = 'select [Id],[Name],[Gender],[Salary],[City] from [Employee] order by ' + @SortCoumn
Execute(@DynamicQuery)


End


Please share if there are other ways of doing this.

2 comments:

  1. CREATE PROC spGetEmployeesSortedUsingDynamicSQL
    @SortCoumn NVARCHAR(10)
    AS
    BEGIN

    DECLARE @DynamicQuery NVARCHAR(100)
    SET @DynamicQuery = 'select * from [Employee] order by '
    + @SortCoumn
    EXECUTE ( @DynamicQuery
    )
    END

    ReplyDelete
  2. Option 2 is vulnerable to sql injection

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us