Tuesday, February 25, 2014

Filter rows based on Where condition AND-OR ?

Why do you want Dynamic sql?


Try the below:



create proc sp_test(@Addnedum int = NULL,@Claimno int = NULL,@Desc int=null)
as
Begin
Set @Addnedum = (Select Case when LEN(LTRIM(RTRIM(@Addnedum)) = 0 Then NULL Else @Addnedum End)
Set @Claimno = (Select Case when LEN(LTRIM(RTRIM(@Claimno)) = 0 Then NULL Else @Claimno End)
Set @Desc = (Select Case when LEN(LTRIM(RTRIM(@Desc)) = 0 Then NULL Else @Desc End)

Select * From Tablename where
AddendumNo = ISNULL(@A1,AddendumNo) and
ClaimNo = ISNULL(@B1,Claimno) and
Description = ISNULL(@C1,Description)
End

Exec sp_test @Addnedum = 'A1'


No comments:

Post a Comment