title: How to filter records with multiple condition based on stored procedure
body: <p>I have a school management system. I just created a stored procedure for retrieving a list of students from the database. If I just pass in null to all parameters, the stored procedure works fine, but when I add some parameter values, then I get this error:</p>
  
  <blockquote>
    <p>Msg 50000, Level 15, State 1, Procedure GetStudentList, Line 91<br>
    Incorrect syntax near the keyword 'and'</p>
  </blockquote>
  
  <p>Here is my stored procedure code:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[GetStudentList]
      (@rollno VARCHAR(5) = NULL,
       @name VARCHAR(25)= NULL,
       @classid INT = NULL,
       @sectionid INT = NULL)
  AS
      DECLARE @qry NVARCHAR(MAX)
      DECLARE @sql NVARCHAR(MAX)  
      DECLARE @rollcond NVARCHAR(5)  
      DECLARE @namecond NVARCHAR(25)  
      DECLARE @classcond NVARCHAR(10)
      DECLARE @sectioncond NVARCHAR(10)
  BEGIN
  BEGIN TRY
      SET @qry = N'select PK_PID as ID,
         StudentName as Name
        ,Gender 
        ,FatherName as Parents
        ,Address
        ,format(DOB,''dd/MM/yyyy'') as DOB
        ,EmailID
        ,AdmissionDate
        ,(select [ClassName] from dbo.classes where classes.PK_PID = s.Class) AS Class   
        ,(select [SectionName] from dbo.sections where sections.PK_PID = s.Section) AS Section   
        ,RollNo
        ,MobileNo
        ,Active   
        ,StudentPhoto
    from dbo.students s where 1=1'
  
    IF @rollno is null  
    BEGIN  
        SET @rollcond = ''  
    END  
    ELSE  
    BEGIN  
        SET @rollcond = ' and s.RollNo like ''%'+@rollno+'%'' '  
    END
  
    IF @name IS NULL
    BEGIN  
        SET @namecond = ''  
    END  
    ELSE  
    BEGIN  
        SET @namecond = ' and s.StudentName like ''%'+@name+'%'' '  
    END
  
    IF @classid IS NULL 
    BEGIN  
        SET @classcond = ''  
    END  
    ELSE  
    BEGIN  
        SET @classcond =' and s.Class = '+CONVERT(INT,@classid)+' '    
    END
  
    IF @sectionid IS NULL
    BEGIN  
        SET @sectioncond = ''  
    END  
    ELSE  
    BEGIN  
        SET @sectioncond =' and s.Section = '+CONVERT(INT,@sectionid)+' '    
    END
  
    SET @sql = @qry + @rollcond + @namecond + @classcond + @sectioncond
    --+' order by ID asc '
  
    EXEC sp_executesql @sql
  END TRY
  BEGIN CATCH 
      DECLARE @ErrorMessage NVARCHAR(4000); 
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;
  
      SELECT 
          @ErrorMessage = ERROR_MESSAGE(), 
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();
  
      RAISERROR (@ErrorMessage, -- Message text.
                 @ErrorSeverity, -- Severity.
                 @ErrorState); -- State.
  END CATCH;
  END
  </code></pre>
  
  <p>What's going wrong here? Please guys help me...</p>
  
  <p><strong>----------------updated------------------------</strong></p>
  
  <pre><code>USE [SM_00001]
  GO
  /****** Object:  StoredProcedure [dbo].[GetStudentList]    Script Date: 11/7/2019 8:56:52 AM ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER PROCEDURE [dbo].[GetStudentList]
  (
      @searchtext varchar(200) = null,
      @classid int = null,
      @sectionid int = null
  )
  AS
  BEGIN
  
  --new script
  select [PK_PID] as ID,
       [StudentName] as Name
      ,[Gender] 
      ,[FatherName] as Parents
      ,[Address]
      ,format([DOB],'dd/MM/yyyy') as DOB
      ,[EmailID]
      ,[AdmissionDate]
      ,C.[ClassName] as Class   
      ,Section.[SectionName] as Section
      ,[RollNo]
      ,[MobileNo]
      ,[Active]   
      ,[StudentPhoto]
  from [dbo].[students] as s 
  
  outer apply (select isnull([ClassName],'Default Class') from [dbo].[classes] where [classes].[PK_PID] = s.[Class]) as C
  outer apply (select isnull([SectionName],'Default Section') from [dbo].[sections] where [sections].[PK_PID] = s.[Section]) as Section 
  
  where (@searchtext is null  or s.[StudentName] LIKE '%'+@searchtext+'%'
                              or s.[Gender] LIKE '%'+@searchtext+'%'
                              or s.[FatherName] LIKE '%'+@searchtext+'%'
                              or s.[Address] LIKE '%'+@searchtext+'%'
                              or s.[DOB] LIKE '%'+@searchtext+'%'
                              or s.[Address] LIKE '%'+@searchtext+'%'
                              or s.[MobileNo] LIKE '%'+@searchtext+'%'
                              or s.[EmailID] LIKE '%'+@searchtext+'%')
  
  and (@classid is null or s.[Class] = CONVERT(INT,@classid))
  and (@sectionid is null or s.[Section] = CONVERT(INT,@sectionid))
  
  
  --old script
  --select PK_PID as ID,
  --    StudentName as Name
  --    ,Gender 
  --    ,FatherName as Parents
  --    ,Address
  --    ,format(DOB,'dd/MM/yyyy') as DOB
  --    ,EmailID
  --    ,AdmissionDate
  --    ,(select [ClassName] from dbo.classes where classes.PK_PID = s.Class) AS Class   
  --    ,(select [SectionName] from dbo.sections where sections.PK_PID = s.Section) AS Section   
  --    ,RollNo
  --    ,MobileNo
  --    ,Active   
  --    ,StudentPhoto
  --from dbo.students s order by ID asc
  END
  </code></pre>
  
  <p>i just get sql error here :</p>
  
  <pre><code>Msg 8155, Level 16, State 2, Procedure GetStudentList, Line 36
  No column name was specified for column 1 of 'C'.
  Msg 8155, Level 16, State 2, Procedure GetStudentList, Line 38
  No column name was specified for column 1 of 'Section'.
  Msg 207, Level 16, State 1, Procedure GetStudentList, Line 27
  Invalid column name 'ClassName'.
  Msg 207, Level 16, State 1, Procedure GetStudentList, Line 28
  Invalid column name 'SectionName'.
  </code></pre>
  
  <p><strong>-----------------------update 2--------------------------</strong></p>
  
  <pre><code>CREATE TABLE [dbo].[students](
      [PK_PID] [int] IDENTITY(1,1) NOT NULL,
      [StudentName] [varchar](25) NULL,
      [Gender] [varchar](10) NULL,
      [FatherName] [varchar](25) NULL,
      [MotherName] [varchar](25) NULL,
      [DOB] [datetime] NULL,
      [Religion] [varchar](15) NULL,
      [BloodGroup] [varchar](5) NULL,
      [FatherOccupation] [varchar](25) NULL,
      [EmailID] [varchar](150) NULL,
      [AdmissionDate] [datetime] NULL,
      [AdmissionID] [varchar](25) NULL,
      [Class] [int] NULL,
      [Section] [int] NULL,
      [RollNo] [varchar](5) NULL,
      [Address] [varchar](200) NULL,
      [BIO] [varchar](200) NULL,
      [MobileNo] [varchar](15) NULL,
      [PhoneNo] [varchar](15) NULL,
      [Active] [bit] NULL,
      [Entry_By] [int] NULL,
      [Enty_date] [smalldatetime] NULL CONSTRAINT [DF_students_Enty_date]  DEFAULT (getdate()),
      [StudentPhoto] [image] NULL,
   CONSTRAINT [PK_students] PRIMARY KEY CLUSTERED 
  (
      [PK_PID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  
  CREATE TABLE [dbo].[classes](
      [PK_PID] [int] IDENTITY(1,1) NOT NULL,
      [ClassName] [varchar](25) NULL,
      [Entry_By] [int] NULL,
      [Entry_Date] [smalldatetime] NULL CONSTRAINT [DF_classes_Entry_Date]  DEFAULT (getdate()),
   CONSTRAINT [PK_classes] PRIMARY KEY CLUSTERED 
  (
      [PK_PID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  
  CREATE TABLE [dbo].[sections](
      [PK_PID] [int] IDENTITY(1,1) NOT NULL,
      [SectionName] [varchar](10) NULL,
      [Entry_By] [int] NULL,
      [Entry_Date] [smalldatetime] NULL CONSTRAINT [DF_sections_Entry_Date]  DEFAULT (getdate()),
   CONSTRAINT [PK_sections] PRIMARY KEY CLUSTERED 
  (
      [PK_PID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>