创建一个可以处理多个复选框选择的sql查询 [英] Create a sql query that can handle multiple check box selections
问题描述
我有一个包含 3 个复选框下拉列表的表单,允许从每个控件中进行多项选择.
I have a form with 3 check box dropdown lists enabling multiple selection from each control.
为了说话,可以说它是我正在查询的住宿表,复选框下拉列表是住宿名称"、公司"和夜晚".
Lets say for talking sake its an accommodation table I am querying and the check box dropdown lists are 'AccommodationName', 'Company', and 'Nights'.
因此,我可能会从每个控件传入多个值,并且我想返回与所有数据输入相关的聚合查询.
So potentially I could be passing in multiple values from each control and I want to return an aggregated query relevant to all data input.
我应该如何处理这个查询?
How should I be going about this query?
查询必须是动态 sql 吗?
Is the query going to have to be dynamic sql?
请注意,我使用的是 sql server 2005.
Please note, I am using sql server 2005.
推荐答案
你需要在你的数据库中创建一个拆分函数,
You will need to create a split function inside you database,
CREATE FUNCTION [dbo].[split]
(
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
)
RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
存储过程
然后您需要创建一个存储过程,它将动态构建 sql 查询并使用此拆分函数来处理作为逗号分隔列表传递的多个值.
Stored Procedure
Then you need to create a stored procedure which will build sql query dynamically and use this split function to handle multiple values passed as a comma deliminated list.
CREATE PROCEDURE GetData
@AccommodationName VARCHAR(1000) = NULL,
@Company VARCHAR(1000) = NULL,
@Nights VARCHAR(1000) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N' SELECT * FROM TableName WHERE 1 = 1 '
+ CASE WHEN @AccommodationName IS NOT NULL
THEN N' AND AccommodationName IN (SELECT Val FROM dbo.split(@AccommodationName )) '
ELSE N'' END
+ CASE WHEN @Company IS NOT NULL
THEN N' AND Company IN (SELECT Val FROM dbo.split(@Company)) '
ELSE N'' END
+ CASE WHEN @Nights IS NOT NULL
THEN N' AND Nights IN (SELECT Val FROM dbo.split(@Nights)) '
ELSE N'' END
EXECUTE sp_executesql @SQL
,N'@AccommodationName VARCHAR(1000), @Company VARCHAR(1000), @Nights VARCHAR(1000)'
,@AccommodationName
,@Company
,@Nights
END
这篇关于创建一个可以处理多个复选框选择的sql查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!