searching with different paramater

habib ullah

8y817

hello brother

i am searching in database i have passing the multiple paramater

and all the paramater they are searching but the rollnumber is not working

my code is below

ALTER PROCEDURE [School].[SurcheStudent]

(

@StudentID INT=null,

@ClassAdmitted_ID INT=null,

@Section int=null,

--@IsActive bit=null,

@StudentRegNo varchar(50)=null,

@StudentName varchar(50)=null,

@StudentFname varchar(50)=null,

@RollNo int=null

)

BEGIN

--DECLARE @IsActive int

--if @IsActive=1

--Begin

SELECT ST.StudentID ,ST.StudentName,St.FatherName,St.FatherCNIC,St.StudentAddress,SCM.CurrentClass_ID,SCM.CurrentSection_ID,

SCM.RollNumber,

ST.Account_ID,

ST.StudentRegNo

FROM School.StudentInformation ST INNER JOIN School.StudentClassMapping SCM ON ST.StudentID=Scm.Student_ID

WHERE SCM.CurrentClass_ID=@ClassAdmitted_ID AND SCM.CurrentSection_ID=@Section

AND ST.StudentName LIKE '%'+ISNULL(@StudentName,St.StudentName)+'%'AND ST.FatherName LIKE '%'+ISNULL(@StudentFname,St.FatherName)+'%' AND RollNo=isnull(@RollNo,SCM.RollNumber)

AND ST.StudentID=ISNULL(@StudentID,St.StudentID) AND ST.StudentRegNo LIKE '%'+@StudentRegNo+'%' AND ST.IsActive=1

END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
Accepted answer
forgot check the data in the "SCM.RollNumber" look for NULLS
0
Shahriar Nikkhah8y ago
First of all lets orgenize your script
ALTER PROCEDURE [School].[SurcheStudent]
(
        @StudentID          INT         =   null
    ,   @ClassAdmitted_ID   INT         =   null
    ,   @Section            int         =   null
    --, @IsActive           bit         =   null
    ,   @StudentRegNo       varchar(50) =   null
    ,   @StudentName        varchar(50) =   null
    ,   @StudentFname       varchar(50) =   null
    ,   @RollNo             int         =   null
)
AS
BEGIN
--DECLARE @IsActive int
--if @IsActive=1
--Begin
    SELECT  ST.StudentID            ,   ST.StudentName      ,   St.FatherName
        ,   St.FatherCNIC           ,   St.StudentAddress   ,   SCM.CurrentClass_ID
        ,   SCM.CurrentSection_ID   ,   SCM.RollNumber      ,   ST.Account_ID
        ,   ST.StudentRegNo
    FROM School.StudentInformation ST
    INNER JOIN School.StudentClassMapping SCM ON ST.StudentID   =   Scm.Student_ID
    WHERE   SCM.CurrentClass_ID     =   @ClassAdmitted_ID
        AND SCM.CurrentSection_ID   =   @Section
        AND ST.StudentName  LIKE '%'+   ISNULL(@StudentName,St.StudentName) + '%'
        AND ST.FatherName   LIKE '%'+   ISNULL(@StudentFname,St.FatherName) + '%'
        AND RollNo                  =   ISNULL(@RollNo , SCM.RollNumber)
        AND ST.StudentID            =   ISNULL(@StudentID,St.StudentID)
        AND ST.StudentRegNo LIKE '%'+@StudentRegNo+'%'
        AND ST.IsActive=1
END
now its readable
try to comment out the one line at a time from the WHERE Clause and see which line is causing the issue, the check the data type for example RoleNumber can be a varchar in studentclassmapping but in the SP its a INT

I feel that it looks like a type miss match

good luck
+1