Building dynamic where condition in SQL statement

You cannot simply put your variable in normal SQL as you have in this line:

select * from table_name where @where;

You need to use dynamic SQL. So you might have something like:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Table_Name WHERE 1 = 1 ';
DECLARE @Params NVARCHAR(MAX) = '';

IF @Vendor_Name IS NOT NULL
    BEGIN
        SET @SQL += ' AND UPPER(vendors.VENDOR_NAME) LIKE ''%'' + UPPER(@VendorNameParam) + ''%''';
    END
ELSE IF @Entity IS NOT NULL
    BEGIN
        SET @SQL += ' AND headers.ORG_ID = @EntityParam';
    END;

EXECUTE SP_EXECUTESQL @SQL, N'@VendorNameParam VARCHAR(50), @EntityParam INT', 
                    @VendorNameParam = @Vendor_Name, @EntityParam = @Entity;

I assume your actual problem is more complex and you have simplified it for this, but if all your predicates are added using IF .. ELSE IF.. ELSE IF, then you don't need dynamic SQL at all, you could just use:

IF @Vendor_Name IS NOT NULL
    BEGIN
        SELECT  * 
        FROM    Table_Name
        WHERE   UPPER(vendors.VENDOR_NAME) LIKE '%' + UPPER(@Vendor_Name) + '%';
    END
ELSE IF @Entity IS NOT NULL
    BEGIN
        SELECT  * 
        FROM    Table_Name
        WHERE   headers.ORG_ID = @Entity;
    END

Use this :

Declare @Where NVARCHAR(MAX) 

...... Create your Where

DECLARE @Command NVARCHAR(MAX) 
Set @Command = 'Select * From SEM.tblMeasureCatalog AS MC ' ;

If( @Where <> '' )
   Set @Comand = @Command + ' Where ' + @Where

Execute SP_ExecuteSQL  @Command

I tested this and it Worked