Dynamic queries in sql server

Introduction

Dynamic queries in SQL are a single set of SQL statements which could be stored in a variable and executed dynamically. There are various methods from which we can do this in SQL Server.

Now lets check how we can do this in a practical approach -

To Demonstrate this lets create a stored procedure with dynamic queries in SQL Server and then execute the stored procedure to check the results. Below is the sample stored procedure with dynamic queries in SQL Server -

/****** Object:  StoredProcedure [dbo].[SP_GetStudentDetails]    Script Date: 02/07/2015 20:47:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_GetStudentDetails]
@classID INT = 0,
@teacherID INT = 0,
@studentName VARCHAR(50) =''
AS
BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 --first option
 
 DECLARE @whereclause VARCHAR(200) = ''
 DECLARE @Fullstmt VARCHAR(2000)

 --  dynamic queries in SQL Server for filtering based on Class
 IF @classID <> 0 
 BEGIN
   SET @whereclause += 'AND Class_ClassID = ' + CAST(@classID AS VARCHAR(20))
 END

-- dynamic queries in SQL Server for filtering based on teacher.
IF @teacherID <> 0 
 BEGIN
   SET @whereclause += 'AND Teacher_TeacherID = ' + CAST(@teacherID AS VARCHAR(20))
 END

 PRINT @studentName

-- dynamic queries in SQL Server for filtering based on student name.
IF @studentName <> '' 
 BEGIN
  SET @whereclause += 'AND StudentName LIKE  ''' + '%' + @studentName + '%' + ''' ' 
  
 END

 SET @Fullstmt = 
  'SELECT * FROM Students WHERE 1=1 ' + @whereclause
 
 EXECUTE(@Fullstmt) 
 
END

The above stored procedure does nothing fancy when the values are passed for the parameters - "Teacher ID", "Class ID" and "Student Name" it will filter the students based on that.

 
EXEC SP_GetStudentDetails  @studentName = 'Student1'

Finally the result will look like this -

Thanks for reading and please give comment below.

How to find tables used in stored procedure in sql server

In this "How to find tables used in stored procedure in sql server"article i am going to discuss about some SQL queries which are useful in our real time projects. We might have done these things manually but now lets see how useful these queries turns out to be.

First SQL query what i wanted to discuss is -



How to find all stored procedure names which uses the given table

Query -

SELECT DISTINCT 
 obj.Name
 ,com.Text
FROM sysobjects obj 
     INNER JOIN syscomments com ON obj.ID = com.ID
WHERE obj.Type = 'P' AND com.Text LIKE '%' + 'yourtablename' + '%'
ORDER BY obj.Name

Lets create a function use this query in function -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllStoredProcsByTableName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllStoredProcsByTableName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all stored procedure details which is using given table
-- =============================================
CREATE FUNCTION FN_GetAllStoredProcsByTableName 
( 
 @tableName VARCHAR(50)
)
RETURNS 
@SPDetails TABLE 
(
 Name VARCHAR(50)
 ,Text VARCHAR(5000)
) 
AS
BEGIN
 
 INSERT INTO @SPDetails
 (
  Name,
  Text
 )
 SELECT DISTINCT 
  obj.Name
  ,com.Text
 FROM sysobjects obj 
  INNER JOIN syscomments com on obj.ID = com.ID
        WHERE obj.Type = 'P' AND com.Text LIKE '%' + @tableName + '%'
        ORDER BY obj.Name
 
 RETURN;
END

As you can see in the above function, table name is passed as an parameter and this table name is searched in all the stored procedures (Type = 'P'). Now lets execute the above function and lets see how the results will look like.

Before executing this function i have created tables "Student" and "Class"(Here i am not giving the table structures as these are trivial)and created a stored procedures "SP_GetAllStudents" and "SP_GetAllClasses" for demonstration purpose, which returns all the students and class details respectively nothing fancy.
Stored procedure SP_GetAllStudents -

CREATE PROCEDURE [dbo].[SP_GetAllStudents]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    SELECT 
 Classes.ClassID 
 ,Students.StudentID
 ,Students.StudentName
    FROM Classes
     INNER JOIN Students ON Classes.ClassID = Students.ClassID
END

Now lets execute the function -

SELECT * FROM dbo.FN_GetAllStoredProcsByTableName('students')

Above function returns below resultset -

Second stored procedure is not filtered because in that stored procedure only "Class" table is used.
Now lets move onto the next SQL query -

How to find tables used in stored procedure in sql server

Query -

SELECT DISTINCT 
  obj2.Name
FROM sys.sysobjects obj
 INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
 INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
WHERE obj.Type = 'P' and obj.Name = 'SP_GetAllStudents'

Now lets create a new function as we created earlier -

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FN_GetAllTableNamesBySpName') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION FN_GetAllTableNamesBySpName
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Abhijith
-- Create date: 12/25/14
-- Description: Find all tables used in given SP name
-- =============================================
CREATE FUNCTION FN_GetAllTableNamesBySpName 
( 
 @SPName VARCHAR(50)
)
RETURNS 
@TableNames TABLE 
(
 Name VARCHAR(50)
) 
AS
BEGIN
 
 INSERT INTO @TableNames
 (
  Name
 )
 SELECT DISTINCT 
  obj2.Name
 FROM sys.sysobjects obj
  INNER JOIN sys.sysdepends dep ON obj.ID = dep.ID
  INNER JOIN sys.sysobjects obj2 ON obj2.ID = dep.DepID
 WHERE obj.Type = 'P' and obj.Name = @SPName
 
 RETURN;
END

As you can see above stored procedure name is passed as a parameter and table names are searched. This will give below result set once executed with
"SP_GetAllStudents" -

SELECT * FROM dbo.FN_GetAllTableNamesBySpName('SP_GetAllStudents')

Hope this article is useful. Please comment your thoughts.

Skip and take in sql server 2008

As we all know in LINQ we have the functionality of Skip and Take on list of objects , where we can skip some rows of list of objects and take the required list of objects. So here in this - "skip and take in sql server 2008" article i will try to explain the same functionality in SQL Server.
Before that lets see how skip and take functions work in LINQ -
To explain this i will create a class called "Employee.cs" as below -

//Example for Skip and take in sql server 2008
public class Employee
{
 public int ID { get; set; }
 public string Name { get; set; }
}

Now lets create a method which will return the list of employees -

//Example for Skip and take in sql server 2008
public static List<Employee> GetEmployees()
{
 List<Employee> employees = new List<Employee>();

 employees.Add(new Employee() { ID = 1, Name = "Jack" });
 employees.Add(new Employee() { ID = 2, Name = "Aaron" });
 employees.Add(new Employee() { ID = 3, Name = "Peter" });
 employees.Add(new Employee() { ID = 4, Name = "David" });
 employees.Add(new Employee() { ID = 5, Name = "Abel" });
 employees.Add(new Employee() { ID = 6, Name = "Adam" });
 employees.Add(new Employee() { ID = 7, Name = "Brian" });

 return employees;
}

Now my intention would be to get 2 employees from the above list namely "Abel" and "Adam" and skip first 4 employees of the list.

//Example for Skip and take in sql server 2008
List selectedEmps = emps.Skip(4).Take(2).ToList();

Now lets get into SQL and check the same functionality -
For demonstration purpose i will create a table called "Employee" and insert the same list of employees into that table.

CREATE TABLE Employee
(
 ID INT IDENTITY(100,1),
 NAME VARCHAR(50)
)

INSERT INTO Employee
VALUES
   ('Jack')
   ,('Aaron')
   ,('Peter')
   ,('David')
   ,('Abel')
   ,('Adam')
   ,('Brian')

Now i will go ahead and create a table valued function to get the table with required number of records.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION FN_TakeNRows
(
 @startRowNo INT = 1
 ,@endRowNo INT = 1
)
RETURNS 
@filteredRows TABLE 
(
 ID INT
 ,Name VARCHAR(50)
 ,UniqueID INT
)
AS
BEGIN
 
 INSERT INTO @filteredRows
 (
  ID,
  Name,
  UniqueID
 )
 SELECT TOP (@endRowNo) allRows.ID, allRows.Name, allRows.uniqueID
 FROM
 (
  SELECT 
   ID
   ,Name
   ,ROW_NUMBER() over(order by ID) as uniqueID
  FROM Employee
 ) allRows
 WHERE allRows.uniqueID > @startRowNo
 
 RETURN;
END
GO

As in LINQ query we will pass the values 4 and 2 as shown below to get 2 employee records. Execute statement of the function is as below -

SELECT * FROM FN_TakeNRows (4, 2)

So here in the above execute statement similar to LINQ query it skips 4 employee records and takes 2 employee records.
The result is below -

Hope this article is useful. Please comment your feedback.

go keyword in sql server 2008

Till now we know that “GO” keyword been used as a batch separator(which is default configured). "GO" is not a statement but a command to the server to commit the current batch to the Database.You can not use "GO" inside a stored procedure. If you would try, the definition of the procedure will end there, and the rest will be a separate batch. 

 Now lets see what you can do with this keyword.

In the above query i am simply creating a temparory table and i am trying to use “GO” keyword which will execute the insert statement 3 times and adds the value “Test” into the column “Name” of @abhiTable.

Same thing if i try for Table Variable (Assuming you know the difference between Temporary Table and Table Variable) the same thing does not work because, SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

Hope this helps.

Dynamic order by clause in sql server

Introduction

The ORDER BY keyword is used to sort the result set by one or multiple columns and it sorts the records in ascending order by default. To sort the records in a descending order we have to use the DESC keyword.

Order By Columns Dynamically

So here i am going to explain how we can use Order By clause to sort the column values dynamically.

Lets create a sample table and populate the values in that -

DECLARE @employee TABLE
( 
 ID INT IDENTITY(1,1)
 ,Name VARCHAR(50)
 ,Gender VARCHAR(20)
 ,DOB DATETIME
 ,TotProjects INT
)

So here the column which is to be sorted and direction of sorting is passed as a variable. So the above table contains columns “Name” which is VARCHAR, “DOB” which DATETIME and “TotProjects” which is INT. So this is intentional to have different datatypes of columns in my table. 

So now lets use the query to sort the column values dynamically –

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'ASC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
      THEN
        CASE 
           WHEN @searchCol = 'NAME' THEN Name 
           WHEN @searchCol = 'GENDER' THEN Gender
           WHEN @searchCol = 'DOB' THEN DOB
           WHEN @searchCol = 'PROJECTS' THEN TotProjects
        END 
  END DESC,
  CASE WHEN @direction = 'ASC'
      THEN
          CASE 
             WHEN @searchCol = 'NAME' THEN Name 
             WHEN @searchCol = 'GENDER' THEN Gender
             WHEN @searchCol = 'DOB' THEN DOB
             WHEN @searchCol = 'PROJECTS' THEN TotProjects
          END
  END ASC

So as in the above figure you can see “CASE” statement is being used in Order By clause. As you observe the columns are sorted based on variable – “@direction“. Column name is also passed from an variable called – “@searchCol“.

So when i tried to execute this with @searchCol setting to “NAME” and @direction setting to “ASC” i will get the below error -

When i googled this, i got multiple links and many of them says – “If you have multiple datatypes in the same CASE statement then it causes this problem“.

So after seeing this i changed my query to have same datatype and it looks like this -

DECLARE @searchCol VARCHAR(50) = 'NAME' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @direction = 'DESC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END 
 END DESC,
 CASE WHEN @direction = 'ASC'
  THEN
  CASE 
   WHEN @searchCol = 'NAME' THEN Name 
   WHEN @searchCol = 'GENDER' THEN Gender
   WHEN @searchCol = 'DOB' THEN CAST(DOB AS VARCHAR(20))
   WHEN @searchCol = 'PROJECTS' THEN CAST(TotProjects AS VARCHAR(20)) 
  END
 END ASC

So in the above screenshot you can see i have used “CAST” to change the datatype of “DOB” and “TotProjects” so now all conditions returns same VARCHAR type. Now i will execute this query with same old paramters  "NAME" and "DESC" - it works.

Now i will execute the same query with @searchCol – “PROJECTS” and @direction - “DESC“. Now i will get results like this -

Oh ho !! it did not work !! Seems like “TotProjects” are not sorting properly.

Now we will go with one more solution to this problem -

DECLARE @searchCol VARCHAR(50) = 'PROJECTS' 
DECLARE @direction VARCHAR(5) = 'DESC' 

SELECT *
FROM @employee
ORDER BY
 CASE WHEN @searchCol = 'NAME' AND @direction = 'ASC' THEN Name END ASC,
 CASE WHEN @searchCol = 'NAME' AND @direction = 'DESC' THEN Name END DESC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'ASC' THEN Gender END ASC,
 CASE WHEN @searchCol = 'GENDER' AND @direction = 'DESC' THEN Gender END DESC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'ASC' THEN DOB END ASC,
 CASE WHEN @searchCol = 'DOB' AND @direction = 'DESC' THEN DOB END DESC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'ASC' THEN TotProjects END ASC,
 CASE WHEN @searchCol = 'PROJECTS' AND @direction = 'DESC' THEN TotProjects END DESC

and this query works for all datatypes and no where we are changing the datatype. In the above query we are checking both direction and sort column in one condition and it runs good. So performance wise also it works good.

Hope it helps.

Sql server context_info trigger

What is Trigger ?

A trigger is a database object that is attached to a table or we can define trigger as a stored procedure that automatically executes when an event occurs in the database server.

Triggers are classified into two types mainly-

• Instead of Trigger
• After Trigger

How can i access the tables in Triggers

When we update a record on the table where trigger is created, the magic tables “INSERTED” and “DELETED” both will be created, the Old data of the updating record will be available in “DELETED” table and, the new data will be available in “INSERTED” table, while accessing them inside the trigger. These tables are called magic tables of the SQL Server. We can not see these tables in the data base. But we can access these tables from the trigger.

How can i pass the value to Trigger ?

So now we are coming to the point where i need to send the data to trigger. So in case tables “INSERTED” and “DELETED” does not hold the data, and you still need to pass the data from either from parameters of stored procedure or from some variables which are evaluated in stored procedure then you are in right place :-)

Here i am creating the Test tables which are required for the demo. Below is the screenshot of the tables which are added newly in my “Testing” Database.

Now the intension would be to insert a record in table “DataCheck” when some record is inserted in to table “Test“. So that means i am creating a trigger on table “Test” to insert the record in table “DataCheck“.

As you can see table “Test” has columns – ID, NAME
and table “DataCheck” has columns – UserID and Name.

So now lets create a trigger on table “Test“ -

CREATE TRIGGER USP_TR_Test ON dbo.Test
FOR INSERT
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE 
  @commaseparatedStr VARCHAR(200),
  @useridStr VARCHAR(200),
  @nameStr VARCHAR(200)


 SELECT @commaseparatedStr = CAST(CONTEXT_INFO() AS VARCHAR)
 FROM master.dbo.SYSPROCESSES
 WHERE SPID = @@SPID

 SELECT 
  @useridStr = substring(@commaseparatedStr,1,CHARINDEX(',',@commaseparatedStr)- 1),
  @nameStr = substring(@commaseparatedStr,CHARINDEX(',',@commaseparatedStr) + 1, len(@commaseparatedStr) - 1)


 INSERT INTO DataCheck (
  NAME
  ,UserID
 )
 SELECT 
  SUBSTRING(@namestr,CHARINDEX('=',@namestr) + 1, len(@namestr) - 1),
  CAST(SUBSTRING(@useridstr,CHARINDEX('=',@useridstr) + 1, len(@useridstr) - 1) AS INT)
END
GO

So basically it inserts data into table “DataCheck“.

Now lets create a stored procedure which inserts data into table “Test” and calls up trigger on the same table.

CREATE PROCEDURE USP_RP_InsertToTest 
(
 @intUserID INT, 
 @name VARCHAR(50)
)
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @CONTEXT_INFO VARBINARY(128)

 SET @CONTEXT_INFO = CAST('UserID=' + CONVERT(VARCHAR(10), @intUserID) + ',' + 'Name =' + @name AS VARBINARY(128))
 SET CONTEXT_INFO @CONTEXT_INFO

 INSERT INTO Test (NAME)
 VALUES (@name)
END
GO

Here we are using keyword called “CONTEXT_INFO” so this is used to retrieve the context information for the current session and session context information is also stored in the context_info columns.

So “SET CONTEXT_INFO variablename” is used to set the context info.

In the trigger if you observe i am using “CONTEXT_INFO()” to fetch the data from context info and insert into “DataCheck“.

Note that the data i am setting to context info is coming as parameters in stored procedures.

Now you just execute the stored procedure created above like this –

and observe that in table “DataCheck” one record is inserted -

Hope it helped.

Identity_insert is set to off in sql

How many times you might came across this issue -

“An explicit value for the identity column in table ‘***’ can only be specified when a column list is used and IDENTITY_INSERT is ON.”

I would say its common issue which occurs when we are trying to insert the value to “IDENTITY” column in a table.

Lets look at why this issue comes up and what it means -

First what you mean by IDENTITY column -> These are commonly used as primary keys in our database tables.

How this issue comes up ?

Ok to answer this question lets create a sample table in our SQL Server -

Error thrown – "An explicit value for the identity column in table ‘Test’ can only be specified when a column list is used and IDENTITY_INSERT is ON."

Lets solve it now -

Execute this now. Worked !!!!

Simple isn’t it.

Hope its helpful too.