ALTER procedure [search_fields]
(
@drpSelectValue VARCHAR(50) = null,
@txtValue as varchar(10)=null
)
As 
begin
DECLARE @SQL varchar(max) =''
if @drpSelectValue = 'username'
begin
      SET @SQL = 'select * from Userdetails  where  ' + @drpSelectValue +' like (case when '''+@txtValue +''' is not null then '''+ @txtValue+ '%'' else ' + @drpSelectValue +' end)'
end 
else 
Begin
      SET @SQL = 'select * from Userdetails  where  ' + @drpSelectValue +' = (case when '''+@txtValue +''' is not null then '''+ @txtValue+ ''' else ' + @drpSelectValue +' end)'
End
      EXEC (@SQL)
End
GO

exec search_fields 'username', 'king'
exec search_fields 'username', null -- Problem

quote:

---

Originally posted by khtan

what do you expect the result to be when you pass in NULL value to the @txtValue ?

you have to handle that logic when you generate the dynamic sql statement

---

if @drpSelectValue = 'last_name' AND @txtValue IS NOT NULL 
begin
	SET @SQL = 'select * from employees where  ' + @drpSelectValue +' like '''+ @txtValue + '%'' '
end 
ELSE 
	SET @SQL = 'select * from employees'

SET @SQL =
'
    select * from employees where @txtValue is NULL or ' + @drpSelectValue ' = @txtValue'
'