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