Hii all,
I am writing store procedure for searching by UserType,EmailId and by First name from tblUser
I written the following code but i am not getting aspected result please check the where clause and also inner join because when i am changing the where clause conditions its giving result but throwing sql msg==>StateName property is not found i did not get it please check both.
ALTER Procedure [dbo].[Usp_SearchUserDetails]
(
@UserId int=NULL,
@UserType nvarchar(50)=NULL,
@FirstName nvarchar(50)=NULL,
@EmailId nvarchar(50)=NULL,
@StateId nvarchar(10)=NULL,
@DistrictId nvarchar(10)=NULL,
@SqlMsg nvarchar(Max)output
)
As
BEGIN
SET @SqlMsg=''
DECLARE @strQuery nvarchar(max)
BEGIN TRY
SET @strQuery='ROW_NUMBER()Over(Order By U.AddDate Desc) as SerialNo,
U.UserType,
U.EmailId,
U.FirstName,
U.LastName,
U.PhoneNo,
U.MobileNo,
U.[Address],
U.PinCode,
U.Company,
S.StateName,
D.DistrictName,
[Password],
CONVERT(Varchar,U.AddDate,103) as AddDate
from tblUser as U
inner join tblState as S
On U.StateId=S.StateId
Inner Join tblDistrict as D
on U.DistrictId=D.DistrictId
Order by U.AddDate Desc
Where UserType=@UserType And EmailId=@EmailId'
IF(@StateId !='0' And @StateId IS Not Null)
Begin
SET @strQuery= @strQuery +' and U.StateId = '''+ @StateId +''' '
End
IF(@DistrictId != '0' And @DistrictId IS Not Null)
Begin
SET @strQuery=@strQuery + ' and U.DistrictId = ''' +@DistrictId+''' '
End
Begin
SET @strQuery=@strQuery + ' Order By U.UserId Desc'
end
print (@strQuery)
Exec(@strQuery)
END TRY
BEGIN CATCH
SET @SqlMsg=ERROR_MESSAGE()
END CATCH
END