Click here to Skip to main content
14,733,448 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i can try to find the data in sql server by using like in sql.
but my below code is not working properly.

What I have tried:

ALTER procedure [smtpserv_gogts].[sppanmembersearch](
@regno varchar(50)=null,    
@strdate date=null,    
@enddate date =null  )
as
begin
select AppMstId, AppMstRegNo, AppmstFName, AppMstEmailId, AppMstMobileNo,AppMstITPAN,AppMstDOJ,PANCopy, addressproof,aadharbackside,cheque,case(VerifyPAN) when 0 then 'Pending' when 1 then 'Verified' end as VerifyPAN from membermaster 
where (VerifyPAN = 0 AND AppMstITPAN<>'' or PANCopy is NOT NULL) AND appmstregno = ISNULL((appmstregno like @regno + '%'), appmstregno) and cast(AppMstDOJ as date) between isnull(@strdate,cast(AppMstDOJ as date)) and isnull(@enddate,cast(AppMstDOJ as date)) ORDER BY AppMstDOJ DESC;    

end
Posted
Updated 8-May-20 11:05am
Comments
ZurdoDev 8-May-20 15:47pm
   
What does "not working properly" mean?
Member 14743579 8-May-20 16:28pm
   
i got an answer

1 solution

Original Post:
but my below code is not working properly.
That does not make for a very good question:
1. We have no way to know what you are expecting to happen.
2. We don't know what is actually happening.
3. We have no data to troubleshoot this

It also makes it easier for the community to help you if the code you post has some resemblance to being formatted. While it may not matter to SQL Server, it does make it easier for us human-folk to read what is going on and perhaps a hint on your train of thought.
So I formatted it myself:
ALTER procedure [smtpserv_gogts].[sppanmembersearch](
   @regno   varchar(50) = null,
   @strdate date        = null, 
   @enddate date        = null
) as
begin
   select AppMstId
        , AppMstRegNo
        , AppmstFName
        , AppMstEmailId
        , AppMstMobileNo
        , AppMstITPAN
        , AppMstDOJ
        , PANCopy
        , addressproof
        , aadharbackside
        , cheque
        , case(VerifyPAN)
              when 0 then 'Pending'
              when 1 then 'Verified'
          end as VerifyPAN

   from membermaster 

   where (   VerifyPAN   = 0
         AND AppMstITPAN <> ''
         or  PANCopy     is NOT NULL
   )
   AND   appmstregno = ISNULL((appmstregno like @regno + '%'), appmstregno)
   and   cast(AppMstDOJ as date) between isnull(@strdate,cast(AppMstDOJ as date))
                                     and isnull(@enddate,cast(AppMstDOJ as date))

   ORDER BY AppMstDOJ DESC;
end
And the things that stick out to me as being problematic are:
1. You appear to be storing Dates in your table as VarChar or similar.
2. The first portion of the WHERE clause could be problematic with the AND...OR combo.
3. The next portion just does not look right either.

Of course; not knowing what you want, have, and got does not make it easy.
   
Comments
Member 14743579 8-May-20 16:27pm
   
i have already tried this code but it shows error on like. but thanks for your try to solve my problem, i got a correct answer of my problem.
MadMyche 8-May-20 16:30pm
   
This is the same code you posted- it has only been formatted to make it readable. I did point out a few problem areas for you to look at.

And still.... it would be very helpful to know specifically what is not working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900