) [ 数据 </跨度> ] [nvarchar](50) )
通过udt传入的过滤器将如下所示:
现场操作 数据 </跨度> ‘名字’,‘bw’,’ 市场 </跨度> “‘LastVisit’,‘gt’,‘01 .07.2017’
现在我想知道什么是最好的 我有一个包含客户的Person表的SQL Server数据库 数据 </跨度> 。在我的Web应用程序中,弹出搜索框(jqGrid)允许用户指定要在搜索中使用的字段/过滤器以及
通过连接之间的连接来解决这种动态过滤是不可能的 Person 桌子和 @Filters 表参数。如果你没有动态SQL,有15个字段和12个运算符,你最终会得到一个怪物where子句。我怀疑存储过程在这种情况下表现良好。
Person
@Filters
相反,我建议使用动态SQL。这是一个存储过程,您可以将其用作起点:
CREATE PROCEDURE [dbo].[pr_GetPersons] (@Filters dbo.udt_Filter READONLY) AS BEGIN DECLARE @SelectList nvarchar(max), @WhereClause nvarchar(max), @SqlCommand nvarchar(max) SET @SelectList = ISNULL(STUFF((SELECT ', ' + QUOTENAME(Field) AS [text()] FROM @Filters FOR XML PATH('')), 1, 1, ''), '*') SET @WhereClause = STUFF(( SELECT ' AND ' + -- Translate opcodes into SQL Server expressions. CASE Op WHEN 'cn' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data + '%', '''') WHEN 'bw' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME(Data + '%', '''') WHEN 'ew' THEN QUOTENAME(Field) + ' LIKE ' + QUOTENAME('%' + Data, '''') WHEN 'gt' THEN QUOTENAME(Field) + '>' + QUOTENAME(Data, '''') WHEN 'lt' THEN QUOTENAME(Field) + '<' + QUOTENAME(Data, '''') END AS [text()] FROM @Filters FOR XML PATH(''), type).value('.', 'nvarchar(max)'), 1, 5, '') SET @SqlCommand = 'SELECT ' + @SelectList + ' FROM dbo.Person WHERE PersonTypeId IN (1, 2, 3)' + ISNULL(NULLIF(' AND ', @WhereClause) + @WhereClause, '') EXEC(@SqlCommand) END
它仅返回过滤器中的列。如果您希望它返回表中的所有列,无论过滤器中包含什么,请进行设置 @SelectList 变量到 * 。
@SelectList
*
所有过滤器标准都与AND运算符连接。 (在里面 @Fields 变量没有迹象表明如何组合它们。所以我认为AND运算符是必需的。)
@Fields
该 CASE 解释操作码的表达式 @Filters 用于生成where子句的表可以移动到自己的函数中。 QUOTENAME(Data, '''') 在这种情况下用于形成适当的弦 Data 是一个字符串,包含单引号。
CASE
QUOTENAME(Data, '''')
Data
该过程不会检查过滤器字段是否存在 Person 表。应特别注意过滤器值( Data ),特别是对于日期/时间和小数类型,因为没有类型的转换(所有过滤器值在形成where子句时用作字符串),也不检查过滤器值类型是否与列中的列类型匹配 Person 表。
希望能帮助到你。