应用数据库WHERE子句时出现问题。

问题描述：

大家好，

问题描述：

在我的应用程序UI上，有两个过滤器Filter1（内部联系人）和Filter2（我的联系活动），我们可以通过UI选择过滤器，无论是两者还是任何一个。

在数据库中，有一个table tbl_Contacts具有EmployeeID，ContactName，RoleType，Email，Location，Office等列，所选过滤器（内部联系人或我的联系活动）需要应用于EmployeeID和RoleType。

当用户选择内部联系人过滤器时，它将像EmployeeID = @InternalContact一样应用，但是当用户选择我的联系人激活过滤器时，它将应用为EmployeeID = @MyContactActivities and RoleType = ''AccountTemaMember''。

内部联系人可以是从filter1中选择的任何用户，我的联系活动可以是从filter2中选择的任何用户。

其他过滤器也可以应用，而不是ma将应用InternalContact和MyContactActivities过滤器。

结果集应该联系用户（从filter1中选择并从filter2中选择）信息。

在存储过程中应用WHERE子句的最佳方法是什么？动态查询的借口。

Hi all,

Question description :

On my application UI, there are two filter Filter1 (Internal Contact) and Filter2 (My Contact Activities), filter can we selected through UI, either both or any of one.

In the database, there is a table tbl_Contacts having a column EmployeeID,ContactName,RoleType, Email, Location, Office etc., selected filter ("Internal Contact" or "My Contact Activities") needs to be apply on EmployeeID and RoleType.

When user select "Internal Contact" filter then it will apply like "EmployeeID = @InternalContact" but when user select "My Contact Activites" filter then it will apply like "EmployeeID = @MyContactActivities and RoleType = ''AccountTemaMember''".

Internal Contact can be any user selected from filter1 and my contact activities can be any user selected from filter2.

Other filter also can be applied and its not mandatory that InternalContact and MyContactActivities filter will be applied.
The result set should contact both user (selected from filter1 and selected from filter2) information.

What will the best way to apply WHERE clause in the stored procedure? excuse for dynamic query.

答

您好，

检查以下代码

Hi,

Check the following Code

DECLARE @InternalContact INT,@MyContactActivities INT
 
SELECT @InternalContact=10
SELECT  EmployeeID,ContactName,RoleType, Email
FROM tbl_Contact
WHERE ( ((@InternalContact IS NOT NULL AND @MyContactActivities IS NULL AND EmployeeID=@InternalContact) OR @InternalContact IS NULL)
AND ((@MyContactActivities IS NOT NULL AND @InternalContact IS NULL AND EmployeeID=@MyContactActivities AND RoleType = 'AccountTemaMember') OR @MyContactActivities IS NULL))
OR (@InternalContact IS NOT NULL AND @MyContactActivities IS NOT NULL AND(EmployeeID=@InternalContact OR (EmployeeID=@MyContactActivities AND RoleType = 'AccountTemaMember')))

问候，

GVPrabu

Regards,
GVPrabu

Create proc Cntct
@InternalContact int=-1,
@MyContactActivities int=-1
as
BEGIN

select * from tbl_Contact
where (isnull(@InternalContact,-1)!=-1 and EmployeeID=@InternalContact) OR
  (isnull(@MyContactActivities,-1)!=-1 and  EmployeeID=@MyContactActivities AND RoleType = 'ATM')
OR (isnull(@MyContactActivities,-1)=-1 and isnull(@InternalContact,-1)=-1)

END

选择所有记录未选择任何过滤器

如果未选择过滤器，则默认传递-1或不传递该参数

select all records any filter are not selected

if filter is not selected then pass -1 as default or dont pass that parameter

应用数据库WHERE子句时出现问题。

相关推荐