首页 文章

如何将动态创建的过滤器作为SQL Server中存储过程的参数传递并过滤数据？

提问于
浏览 次
1

我如何传递一些 dynamic filters （从SQL Server中的表 tblMarketingCampaignFilters 填充）并将它们与 tblMarketingUsers 中的 TargetColumn 进行比较？

只是为了澄清，我有所有代码来生成和填充过滤器 InputTypes 指定（它可以是数字，日期，字符串或带复选框的布尔值），但该页面应该添加用户想要的数量（ n过滤器） .

这是一个显示我的表是如何定义的图像

tables used

我必须将过滤器与 tblMarketingUsers 中的列（待添加）进行比较 .

例子：

  • LastLoginDate> '20150701'如果是日期，

  • 高级配置文件状态> 50（百分比）如果是数字，

  • 如果选中复选框，则InBothSystems = 0

这是我在页面中的表单的过滤器部分

client-side part

到现在为止，我只有4个过滤器，可能是10个，我不知道 .

谢谢 .

PS：我认为我在 tblMarketingCampaignFilters 中缺少一个 TableTarget ，如果系统必须查看另一个表，不仅仅是 tblMarketingUsers ，而且这并不重要 .

PS2：我正在考虑将过滤器作为文本传递，使用逗号分隔的样式，然后将其插入 transMarketingCampaignFilters ，但仍然不知道如何动态比较它们，我的老板说"use cursors in SQL"

EDIT

感谢您的回复和评论 .

这是我想要做的一种方法 . 我将需要在 tblMarketingCampaignFilters 中添加运算符列（它将存储类似：' = ' , ' < ', ' > ', ' LIKE '），顺便说一句，此存储过程将假定已在表单上提交了包含过滤器的Campaign，并且所有数据存储在 transMarketingCampaignFilters

CREATE PROCEDURE usp_MarketingUsersGetFiltered
    -- Add the parameters for the stored procedure here
    @CampaignId int,
    @Debug BIT = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @sql varchar(max) = '', @Nsql nvarchar(max), @DatabaseId INT;
    -- Insert statements for procedure here 

set @DatabaseId = (SELECT DatabaseId from tblMarketingCampaigns where Id=@CampaignId);
select @sql +=  ' AND '+quotename(f.TargetColumn) + f.Operator + 
            case f.InputType 
            when 'int' then cast(cf.Value as int)
            else quotename(cf.Value,'''')               
            end
from dbo.transMarketingCampaignFilters as cf
join dbo.tblMarketingCampaignFilters as f
on cf.CampaignFilterId = f.Id
where cf.CampaignId = @CampaignId

 set @sql = '
 select MarketingUserId,
        FirstName,
        LastName,
        Email,
        CompanyId
 from dbo.tblMarketingUsers 
 where DatabaseId = @DatabaseId
 '+ @sql+'
 OPTION (RECOMPILE)';

if @Debug = 1 print @sql;
set @sql = @Nsql;
exec sys.sp_executesql @Nsql,N'@DatabaseId INT',@DatabaseId;

END
GO
c# sql-server function stored-procedures sql-function

3 回答

  • 1

    最后，我写了 Javascript 代码（有点复杂）：

    • 创建动态过滤器及其各自的输入

    • 我的实际类型只有4（int，bit，varchar，date）

    • 对于'int'动态创建 <input type="number">

    • 对于'date'动态创建 <input type="text"> ，带有日期插件

    • 等...

    • 我've created an array to store the id'我的过滤器并通过AJAX调用发送到服务器（和完整的表单）

    然后在服务器代码（ C# ）中我编写了代码：

    • 保存广告系列详细信息 .

    • 遍历参数数组 .

    • 保存添加了AddCampaign存储过程提供的CampaignId的每个过滤器（保存活动后），窗体的每个 select 值提供的过滤器ID，以及生成的输入上提供的值 .

    然后在 SQL Server ，我提供了问题，稍加修改：

    CREATE PROCEDURE usp_MarketingUsersGetFiltered
-- Add the parameters for the stored procedure here
@CampaignId int,
@Debug BIT = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sql varchar(max) = '', @Nsql nvarchar(max), @DatabaseId INT;
-- Insert statements for procedure here

set @DatabaseId = (SELECT DatabaseId from tblMarketingCampaigns where Id=@CampaignId);
select @sql +=  ' AND '+quotename(f.TargetColumn) + f.Operation + 
            case f.InputType 
            when 'int' then cf.Value
            when 'bit' then cf.Value
            else quotename(cf.Value,'''')               
            end
from dbo.transMarketingCampaignFilters as cf
join dbo.tblMarketingCampaignFilters as f
on cf.CampaignFilterId = f.Id
where cf.CampaignId = @CampaignId

 set @sql = '
 select MarketingUserId,
        FirstName,
        LastName,
        tblMarketingUsers.Email as UserEmail,
        CompanyId,
        CompanyName,
        Address1,
        Address2,
        City,
        SmartInsightProfile,
        LastLoginDate,
        AdvancedProfileStatus
 from dbo.tblMarketingUsers 
 left join dbo.tblCompanies on tblMarketingUsers.CompanyId=tblCompanies.Id
 where DatabaseId = @DatabaseId
 '+ @sql+'
 OPTION (RECOMPILE)';

if @Debug = 1 print @sql;
set @Nsql = @sql;
exec sys.sp_executesql @Nsql,N'@DatabaseId INT',@DatabaseId;

END
GO

    我的 table 是这样的

    Tables

    其中tblMarketingCampaignFilters存储类似于的行：

    (Id , Name , InputType ,TargetColumn ,Operator)

    (1, 'Advanced Profile Status', 'int', 'AdvancedProfileStatus', '<')

    (2, 'Last Login Date', 'date', 'LastLoginDate', '<')

    (3, 'Geo Location (City)', 'varchar', 'City', 'LIKE')

    最后的结果：

    select MarketingUserId,
        FirstName,
        LastName,
        tblMarketingUsers.Email as UserEmail,
        CompanyId,
        CompanyName,
        Address1,
        Address2,
        City,
        SmartInsightProfile,
        LastLoginDate,
        AdvancedProfileStatus
 from dbo.tblMarketingUsers 
 left join dbo.tblCompanies on tblMarketingUsers.CompanyId=tblCompanies.Id
 where DatabaseId = 3
  AND [City]LIKE'%salta%' AND [SmartInsightProfile]!=1 AND [LastLoginDate]<'20150708' AND [AdvancedProfileStatus]<42
 OPTION (RECOMPILE)

    非常感谢你的帮助！

    我知道这不是最好的方式，但它现在对我有用！

    希望这会帮助别人 .

    回复于
  • 0

    1）将过滤器数据作为用户定义的类型或XML参数传递到存储过程中 .

    2）检查存储过程中灵活参数集的内容，以及最常见的用例调用具有固定SQL内容的子存储过程，以满足最常见的情况 . 这使得这些程序对于其目的而言是最佳的 .

    3）如果过滤器集与任何高频预测模式都不匹配，则使用动态生成的SQL where-子句在存储过程层内生成匹配项 .

    在业务逻辑层生成参数化SQL并在所有情况下针对SQL Server执行它很有吸引力，如果您无法识别任何非常频繁的用例，这可能是您最好的方法，但它缺乏测试能力和东部性能（2）中描述的支化的优化 .

    回复于
  • 0

    将所有可能的过滤器添加为存储过程中的搜索参数 . 如果设置了过滤器，则参数不为null，否则为null . 然后，在where子句中，如果参数不为null，则仅使用该参数 . 像这样的东西：

    WHERE (@LastLoginDate IS NULL OR t.LoginDate<@LastLoginDate) AND
      (@CityId IS NULL OR t.CityId=@CityId)
    回复于

相关问题