Filter Record From DataBase.

USE [Fanzdb]

GO

/****** Object: StoredProcedure [FanzUser].[sp_GetUserCountryFilter] Script Date: 19-03-2020 12:35:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [FanzUser].[sp_GetUserCountryFilter]

@CountryId int=0,

@StartDate datetime =NULL,

@EndDate datetime =Null

as

BEGIN

Declare @sql nvarchar(max)=''

Set @sql='

select um.Userid, um.FullName, um.CountryId, um.CreatedDate, um.EmailId, um.PhoneNumber, um.Authenticationtype, cmt.CountryName

FROM UserMaster um

INNER JOIN CountryMaster cmt ON um.CountryId = cmt.CountryId where 1=1'

If(@CountryId<>0)

BEGIN

Set @sql+= ' and cmt.CountryId='+ Convert(varchar,@CountryId)

END

If(@StartDate is not NULL)

BEGIN

Set @sql+=' and um.CreatedDate >='''+ Convert(varchar,@StartDate) +''''

END

IF(@EndDate is not null)

BEGIN

Set @sql+=' and um.CreatedDate <='''+ Convert(varchar,@EndDate) +''''

END

Set @sql+=' order by um.Userid desc'

Print(@sql)

EXec(@sql)

END

 

This is my sp. I have filter record from date . It's working but i have face a problem I have filter record from 16-03-2020 to 19-03-2020 .  only one record get this . But 3 record my database. Please check this