Stored Procedure

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

SQL Server interview questions on string manipulation functions

The following 2 SQL Server Interview questions were asked when I attended an interview for SQL Server Developer role.

Can you list a few useful string manipulation functions in SQL Server?
LEN(), SUBSTRING(), CHARINDEX(), LEFT(), RIGHT() etc.



Then he asked me, Can you give me one example of where you have used these functions in your experience?
The following is one simple real time example, where we can use LEN(), CHARINDEX() and SUBSTRING() functions. Let us assume we have table as shown below. 


I want you to write a query to find out total number of emails, by domain. The result of the query should be as shown below.

We can use LEN(), CHARINDEX() and SUBSTRING() functions to produce the desired results. Please refer to the query below.

Select SUBSTRING(Email,CHARINDEX('@',Email)+1,(LEN(Email) - CHARINDEX('@',Email))) as EmailDomain, Count(*) as Total From TableName  Group By SUBSTRING(Email,CHARINDEX('@',Email)+1,(LEN(Email) - CHARINDEX('@',Email))) Order by Count(*) Desc

There could be even better ways of producing the same result. If you feel you have a better way of producing the same output, please share using the form below.

What are the different ways to replace NULL values in SQL Server

This interview question is not that common. My friend faced this interview question, when he attended an interview in London. My friend said we can use  COALESCE() in SQL Server. Then the interviewer said, that's very good answer, but do you know of any other way? 

Apart from using COALESCE(), there are 2 other ways to replace NULL values in SQL Server. Let's understand this with an example.

I have a Table tblEmployee, as shown in the diagram below. Some of the Employees does not have gender. All those employees who does not have Gender, must have a replacement value of 'No Gender' in your query result. Let's explore all the 3 possible options we have.


Option 1 : Replace NULL values in SQL Server using ISNULL() function.

Select Name, ISNULL(Gender,'No Gender') as Gender
From tblEmployee


Option 2 : Replace NULL values in SQL Server using CASE.

Select Name, Case  When Gender IS NULL Then 'No Gender' Else Gender End as Gender
From tblEmployee


Option 3 : Replace NULL values in SQL Server using COALESCE() function.

Select Name, Coalesce(Gender, 'No Gender') as Gender
From tblEmployee


These are the 3 options that I can think of at the moment. If you can think of any other option, please post it here.

Advantages of stored procedures

This is a very common sql server interview question. There are several advantages of using stored procedures over adhoc queries, as listed below. 1. Better Performance : Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again. Although adhoc queries also create and reuse plan, the plan is reused only when the query is textual match and the datatypes are matching with the previous call. Any change in the datatype or you have an extra space in the query then, a new plan is created. 2. Better Security : Applications making use of dynamically built adhoc sql queries are highly susceptible to sql injection attacks, where as Stored Procedures can avoid SQL injection attacks completely. 3. Reduced Network Traffic: Stored procedures can reduce network traffic to a very great extent when compared with adhoc sql queries. With stored procedures, you only need to send the name of the procedure between client and server. Imagine the amount of network bandwith that can be saved especially if the stored procedure contains 1000 to 2000 lines of SQL. 4. Better Maintainance and Reusability: Stored procedures can be used any where in the application. It is easier to maintain a stored procedure that is used on several pages as themodfifcations just need to be changed at one place where the stored procedure is defined. On the other hand, maintaining an adhoc sql query that's used on several pages is tedious and error prone, as we have to make modifications on each and every page. If you can think of any other advantage of using stored procedures, please contribute by submitting the form below.