搜索条件的复杂Where子句 [英] Complex Where clause for search criteria
问题描述
以下是我根据公司名称，公司ID，公司税ID进行搜索的代码.
问题:当我通过公司ID时，它返回正确的值.但是，当我通过公司名称时，它将失败，并显示将ERROR转换为INT的错误字符串".但是我想要当Search_Field = 2时它应该调查一下，但是我认为与其搜索标准Criteria Search_Field = 1而不是它是用于公司ID搜索.
Following is my code for the search base on Company Name , Company ID, Company Tax ID.
Problem: When i pass Company ID it return value which is right. But when i pass Company Name it fails with "Conversion ERROR String to INT". But i Want when Search_Field =2 then it should look into it but i think instead of Its still search the Criteria Search_Field = 1. which is for Company ID Search.
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 - Contains
AS
BEGIN
SELECT *
FROM dbo.tbl_Company AS tc
WHERE (( @Search_Field = 1) AND (( Comp_Id = @Search_Criteria AND @Search_Option = 1) OR
( CAST(Comp_Id AS VARCHAR(10)) LIKE '%' + @Search_Criteria + '%' AND @Search_Option = 2 )))
OR (( @Search_Field = 2 ) AND(( Comp_Name = @Search_Criteria AND @Search_Option = 1) OR
( Comp_Name LIKE '%' + @Search_Criteria + '%' AND @Search_Option = 2 ) ))
END
调用存储过程::::，公司名称="Grotanentor Holdings"
:::::::::::::错误::::::::::::
消息245，级别16，状态1，过程usp_GET_User_by_Admin_Employer_Customer，第13行
将varchar值"Grotanentor Holdings"转换为数据类型int时，转换失败.
Calling Store Procedure :::: With Company Name = "Grotanentor Holdings"
:::::::::::: ERROR :::::::::::
Msg 245, Level 16, State 1, Procedure usp_GET_User_by_Admin_Employer_Customer, Line 13
Conversion failed when converting the varchar value ''Grotanentor Holdings'' to data type int.
EXEC @return_value = [dbo].[usp_GET_User_by_Admin_Employer_Customer]
@Search_Field = 2,
@Search_Criteria = N'Grotanentor Holdings',
@Search_Option = 1,
SELECT 'Return Value' = @return_value
GO
调用存储过程::::公司ID = 8，
:::::::::::::成功:::::::::::
Calling Store Procedure :::: With Company ID = 8 ,
:::::::::::: Success :::::::::::
EXEC @return_value = [dbo].[usp_GET_User_by_Admin_Employer_Customer]
@Search_Field = 1,
@Search_Criteria = N'8',
@Search_Option = 1,
SELECT 'Return Value' = @return_value
GO
推荐答案
我找到了解决方案:我使用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[^]
这篇关于搜索条件的复杂Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
