我找到了解决方案:我使用where子句而不是IF语句达到相同的结果

I Found the solution : Instead using where clause i am using IF statement to achieve same result

ALTER PROCEDURE [dbo].[usp_GET_User_by_Admin_Employer_Customer]
    @Search_Field INT , --1 - Company ID, 2 Company Name,3 Company Tax ID
    @Search_Criteria VARCHAR(100) ,
    @Search_Option INT ,--1 - Equals, 2 - Greater Than , 3 - Less Than , 4 -Contains
    @executing_User_Id INT
AS -- Search by Company_ID
    IF ( @Search_Field = 1
         AND @Search_Option = 1
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   ISNUMERIC(@Search_Criteria) = Comp_Id
        
    IF ( @Search_Field = 1
         AND @Search_Option = 2
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Id >  @Search_Criteria 
        
    IF ( @Search_Field = 1
         AND @Search_Option = 3
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Id < @Search_Criteria 
        
    IF ( @Search_Field = 1
         AND @Search_Option = 4
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Id LIKE '%' + @Search_Criteria + '%'
			
		
		-- Search By Company_Name			    
    IF ( @Search_Field = 2
         AND @Search_Option = 1
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   @Search_Criteria = Comp_Name
				
    IF ( @Search_Field = 2
         AND @Search_Option = 4
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Name LIKE '%' + @Search_Criteria + '%'
        
     IF ( @Search_Field = 2
         AND @Search_Option = 2
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Name > @Search_Criteria 
				
    IF ( @Search_Field = 2
         AND @Search_Option =3
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Name < @Search_Criteria 
			   
			    
		 -- Search By Company_Tax_Id			    
	IF ( @Search_Field = 3
         AND @Search_Option = 1
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   @Search_Criteria = Comp_Tax_Id
        
    IF ( @Search_Field = 3
         AND @Search_Option = 2
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE  Comp_Tax_Id > @Search_Criteria 
	 
	 IF ( @Search_Field = 3
         AND @Search_Option = 3
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE  Comp_Tax_Id < @Search_Criteria			
    
	IF ( @Search_Field = 3
         AND @Search_Option = 4
       ) 
        SELECT  *
        FROM    dbo.tbl_Company
        WHERE   Comp_Tax_Id LIKE '%' + @Search_Criteria + '%'

即使您可以正常使用，我也会提出另一种可能性.您还可以动态构建该语句并执行它.看看以下内容:

Even though you got it working, I''ll throw another possibility. You can also build the statement dynamically and execute it. Have a look at the following:

CREATE PROCEDURE [dbo].[usp_GET_User_by_Admin_Employer_Customer]
    @Search_Field INT , --1 - Company ID, 2 Company Name,3 Company Tax ID
    @Search_Criteria VARCHAR(100) ,
    @Search_Option INT ,--1 - Equals, 2 - Greater Than , 3 - Less Than , 4 -Contains
    @executing_User_Id INT
AS 
   DECLARE @sql varchar(2000)
BEGIN
   -- set the start of the statement
   SET @sql = 'SELECT * FROM dbo.tbl_Company WHERE ';

   -- decide the fields
   SET @sql = @sql + 
      CASE @Search_Field 
         WHEN 1 THEN 'Comp_Id '
         WHEN 2 THEN 'Comp_Name '
         WHEN 3 THEN 'Comp_Tax_Id '
      END;
   -- decide the condition
   SET @sql = @sql + 
      CASE @Search_Option 
         WHEN 1 THEN ' = @Search_Criteria '
         WHEN 2 THEN ' > @Search_Criteria '
         WHEN 3 THEN ' < @Search_Criteria '
         WHEN 4 THEN ' LIKE ''%@Search_Criteria%'' '
      END;

   -- execute statement;
   EXEC sp_executesql @sql, N'@Search_Criteria VARCHAR(2000)', Search_Criteria;
END;

那还没有经过测试也没有完成，但是可以让您了解如何构造该语句.

根据情况，此方法可能会(或可能不会)导致更易于维护的过程:)

That is not tested nor complete but to give you the idea how you can also construct the statement.

Depending on the situation this approach may (or may not) lead to more maintainable procedure :)

尝试使用: ^ ]

更多信息:
在存储过程中构建动态SQL [ ^ ]
如何在存储过程之间共享数据 [

Try to use: COALESCE[^]

More at:
Building Dynamic SQL In a Stored Procedure[^]
How to share data between stored procedures[^]