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
when 0 then 'Pending'
when 1 then 'Verified'
end as VerifyPAN
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;
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
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.