Dynamic Filters in SQL Server I have problems with my dynamic filter in SQL Server.

My problem is that the filters aren't working. When I try to execute the stored procedure, I just get all the data without filtering.

I'm new to this. 

alter PROCEDURE Member_WS
    @Program int ='',
    @Card nvarchar(30)= '',
    @Account nvarchar(128) ='',
    @Member bigint =0,
    @Email nvarchar(256)='',
    @Phone nvarchar(20)='', 
    @ResponseType nvarchar ='',
    @FirstName nvarchar =''
AS
    DECLARE @sql varchar(max),
            @filtro varchar(500)

    set @filtro = ''

    if @Program = '' and @Card = '' and @Account = '' and @Member = '' and @Email = '' and @Phone = '' and @FirstName = ''
    set @filtro = ''
    else
    begin
        set @filtro = ''
        if @Program &lt;&gt; ''
            set @filtro = @filtro + ' and' set @filtro =@filtro + '[Program].ProgramID =  convert(varchar,@Program)'
        if @FirstName &lt;&gt; ''
             Select @filtro = @filtro + ' and [member].FirstName= ' + @FirstName
        if @Card &lt;&gt; ''
             Select @filtro = @filtro + ' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber))) + ltrim(str(LS001..[account].AccountNumber)) +  ltrim(str(LS001..[account].verificationDigitSufix))= ' +  @Card
        if @Account &lt;&gt; ''
             Select @filtro = @filtro + ' and [Account].AccountID = ' + @Account
        if @Member &lt;&gt; ''
             Select @filtro = @filtro + ' and [member].MemberID= ' +  convert(varchar,@Member)
        if @Email &lt;&gt; ''
             Select @filtro = @filtro + ' and [user].email= ' + char(39) + @email + char(39)
        if @Phone &lt;&gt; ''
             Select @filtro = @filtro + ' and [user].PhoneNumber= ' + @Phone 
    end
--------------------------------------------
Exec Member_WS @Program = 50
GO 



This is not the full query just the filter part.
I would re-write the whole proc something like this.....

ALTER PROCEDURE Member_WS
    @Program        int             = NULL,
    @Card           nvarchar(30)    = '',
    @Account        nvarchar(128)   ='',
    @Member         bigint          = NULL,
    @Email          nvarchar(256)   ='',
    @Phone          nvarchar(20)    ='', 
    @ResponseType   nvarchar(100)   ='',
    @FirstName      nvarchar(100)   =''
AS
BEGIN
  SET NOCOUNT ON;
    DECLARE @sql nvarchar(max);

set @sql = N' SELECT * FROM TableName WHERE 1 = 1 ' --&lt;-- for example your main query
         + CASE WHEN @Program IS NOT NULL 
           THEN N' AND [Program].ProgramID =  @Program ' ELSE N' ' END
          + CASE WHEN @FirstName &lt;&gt; ''
           THEN N' AND [member].FirstName = @FirstName ' ELSE N' ' END
          + CASE WHEN  @Card &lt;&gt; ''
            THEN N' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber))) 
                   + ltrim(str(LS001..[account].AccountNumber)) 
                   +  ltrim(str(LS001..[account].verificationDigitSufix))= @Card ' ELSE N' ' END
          + CASE WHEN @Account &lt;&gt; ''
            THEN N' and [Account].AccountID = @Account '  ELSE N' ' END
          + CASE WHEN @Member IS NOT NULL 
            THEN N' and [member].MemberID = @Member' ELSE N' ' END
          + CASE WHEN @Email &lt;&gt; ''
            THEN N' and [user].email=  @email ' ELSE N' ' END
          + CASE WHEN @Phone &lt;&gt; ''
            THEN N' and [user].PhoneNumber = @Phone ' ELSE N' ' END

Exec sp_executesql @sql
                  ,N'@Program int , @Card nvarchar(30), @Account nvarchar(128)
                  ,@Member bigint , @Email nvarchar(256), @Phone nvarchar(20)
                  ,@FirstName nvarchar(100)'
                  ,@Program
                  ,@Card
                  ,@Account 
                  ,@Member
                  ,@Email 
                  ,@Phone
                  ,@FirstName


END