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.