ASP.NET Interview Questions – Beginner Level (Part 1)

CREATE PROCEDURE [dbo].[SchoolMstShowFull]                  
(                
@UserID int,            
@UserType   varchar(100),              
 @searchtext varchar(100)=null                    
 )                    
AS                      
BEGIN                
declare            
@LoginID int,            
@Cause varchar(10)            
if(@UserType='SAdmin' and @LoginID=1)                
Begin                
set @LoginID=-1            
set @Cause=''            
end              
else            
Begin                
set @LoginID=@UserID            
set @Cause='SCH'            
end              
             
if(@UserType='SAdmin' and @LoginID=1)                
  BEGIN            
           
       
     select sno=ROW_NUMBER()over(order by(select 1)),a.Address as FullAddress,a.SchoolID,a.School_Name,    
     a.School_Code,a.School_ShortName,a.Phone,a.Fax,a.Website,a.Email,a.Mobile,a.Pin,a.Country,a.City,a.State from SchoolMst a              
     where            
                 
  --( @LoginID=-1 or  b.LoginID=@LoginID) and (@Cause='' or b.Cause=@Cause) and            
           a.IsActive=1 and a.IsDelete=0 and              
    (@searchtext is null or                  
    (a.School_Code=@searchtext or a.School_Code like '%'+@searchtext+'%')                
     or                
    (a.School_Name=@searchtext or a.School_Name like '%'+@searchtext+'%')                
                     
     )                
     END            
     else if(@UserType='SAdmin' and @LoginID!=1)                
      BEGIN            
        select sno=ROW_NUMBER()over(order by(select 1)),a.Address as FullAddress,a.SchoolID,a.School_Name,    
        a.School_Code,a.School_ShortName,a.Phone,a.Fax,a.Website,a.Email,a.Pin,a.Mobile,a.Country,a.City,a.State  from SchoolMst a  join Permission b on a.SchoolID=b.CauseID            
     where            
LoginID=@LoginID and b.Cause='SCH' and              
  --( @LoginID=-1 or  b.LoginID=@LoginID) and (@Cause='' or b.Cause=@Cause) and            
           a.IsActive=1 and a.IsDelete=0 and              
    (@searchtext is null or                  
    (a.School_Code=@searchtext or a.School_Code like '%'+@searchtext+'%')                
     or                
    (a.School_Name=@searchtext or a.School_Name like '%'+@searchtext+'%')                
                     
     )              
         END              
                     
                     
    else if(@UserType='PRNT' )                
       BEGIN            
   select sno=ROW_NUMBER()over(order by(select 1)),a.Address as FullAddress,a.SchoolID,a.School_Name,    
   a.School_Code,a.School_ShortName,a.Phone,a.Fax,a.Website,a.Email,a.Mobile,a.Pin ,a.Country,a.City,a.State from SchoolMst a              
   where            
   a.SchoolID=(select School_ID from StudentMst where  IsActive=1 and IsDelete=0 and  Student_ID= ( select top 1 Student_ID from Parent_Student_Rel where ParentID=@UserID and IsActive=1 and IsDelete=0 ) )            
   and            
      --LoginID=@LoginID and b.Cause='SCH' and              
     --( @LoginID=-1 or  b.LoginID=@LoginID) and (@Cause='' or b.Cause=@Cause) and            
      a.IsActive=1 and a.IsDelete=0 and              
   (@searchtext is null or                  
  (a.School_Code=@searchtext or a.School_Code like '%'+@searchtext+'%')                
   or                
  (a.School_Name=@searchtext or a.School_Name like '%'+@searchtext+'%')                
         )              
     END              
                     
    else if(@UserType='STDNT' )                
        BEGIN            
     select sno=ROW_NUMBER()over(order by(select 1)),a.Address as FullAddress,a.SchoolID,a.School_Name,    
     a.School_Code,a.School_ShortName,a.Phone,a.Fax,a.Website,a.Email,a.Pin,a.Mobile ,a.Country,a.City,a.State from SchoolMst a              
     where            
     a.SchoolID=(select School_ID from StudentMst where  IsActive=1 and IsDelete=0 and  Student_ID=@UserID)          
     and            
   --LoginID=@LoginID and b.Cause='SCH' and              
     --( @LoginID=-1 or  b.LoginID=@LoginID) and (@Cause='' or b.Cause=@Cause) and            
        a.IsActive=1 and a.IsDelete=0 and              
    (@searchtext is null or                  
    (a.School_Code=@searchtext or a.School_Code like '%'+@searchtext+'%')                
    or                
    (a.School_Name=@searchtext or a.School_Name like '%'+@searchtext+'%')                
                         
     )              
         END              
          else if(@UserType='TCHR' )                
        BEGIN            
     select sno=ROW_NUMBER()over(order by(select 1)),a.Address as FullAddress,a.SchoolID,a.School_Name,    
     a.School_Code,a.School_ShortName,a.Phone,a.Fax,a.Website,a.Email,a.Pin,a.Mobile,a.Country,a.City,a.State  from SchoolMst a              
     where            
     a.SchoolID=(select b.SchoolID from StaffMst a join DesignationMst b on a.DesignationID=b.DesignationID  where StaffId=@UserID
  and a.IsActive=1 and a.IsDelete=0
  and b.IsActive=1 and b.IsDelete=0)            
     and            
   --LoginID=@LoginID and b.Cause='SCH' and              
     --( @LoginID=-1 or  b.LoginID=@LoginID) and (@Cause='' or b.Cause=@Cause) and            
        a.IsActive=1 and a.IsDelete=0 and              
    (@searchtext is null or                  
    (a.School_Code=@searchtext or a.School_Code like '%'+@searchtext+'%')                
    or                
    (a.School_Name=@searchtext or a.School_Name like '%'+@searchtext+'%')                
                         
     )              
         END            
                     
END