I have a customer table with Cust_Id, Name, City and search is based upon any or all of the above three. Which one Should I go for ? Dynamic SQL: declare @str varchar(1000) set @str = 'Select [Sno],[Cust_Id],[Name],[City],[Country],[State] from Customer where 1 = 1' if (@Cust_Id != '') set @str = @str + ' and Cust_Id = ''' + @Cust_Id + '''' if (@Name != '') set @str = @str + ' and Name like ''' + @Name + '%''' if (@City != '') set @str = @str + ' and City like ''' + @City + '%''' exec (@str) Simple query: select [Sno],[Cust_Id],[Name],[City],[Country],[State] from Customer where (@Cust_Id = '' or Cust_Id = @Cust_Id) and (@Name = '' or Name like @Name + '%') and (@City = '' or City like @City + '%') Which one should I prefer (1 or 2) and what are advantages? After going through everyone's suggestion , here is what i finally got. DECLARE @str NVARCHAR(1000) DECLARE @ParametersDefinition NVARCHAR(500) SET @ParametersDefinition = N'@InnerCust_Id varchar(10), @InnerName varchar(30),@InnerCity varchar(30)' SET @str = 'Select [Sno],[Cust_Id],[Name],[City],[Country],[State] from Customer where 1 = 1' IF(@Cust_Id != '') SET @str = @str + ' and Cust_Id = @InnerCust_Id' IF(@Name != '') SET @str = @str + ' and Name like @InnerName' IF(@City != '') SET @str = @str + ' and City like @InnerCity' -- ADD the % symbol for search based upon the LIKE keyword SELECT @Name = @Name + '%', @City = @City+ '%' EXEC sp_executesql @str, @ParametersDefinition, @InnerCust_Id = @Cust_Id, @InnerName = @Name, @InnerCity = @City; References : http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p http://msdn.microsoft.com/en-us/library/ms175170.aspx