逗号分隔的字符串过滤参数 [英] comma separated string filter parameter
问题描述
我使用复选框列表作为sql查询的过滤器。我把这些值作为csv传递到目前为止......所以差不多好。
问题是,如果我选择多个值,那么我将忽略/跳过没有至少(不确定如何放置)所有选定值的记录。
ex ...
(破折号仅用于格式化目的)
person ------- person / sport --------- sport
_________________________________________
a ------------ a |棒球------棒球
b ------------ a |足球------足球
c ------------ a |棒球------篮球
-------------- b |棒球
-------------- b |足球
-------------- c |棒球
-------------- c |足球
-------------- c |篮球
如果我选择棒球,那么人,a,b和c将被退回。
如果我选择棒球和足球然后所有3个人都将出现在gridview中。
但是,如果我选择任何组合与baseketball,那么b将不会显示,因为在很多桌子中,b没有篮球。 />
例如,如果我选择足球和篮球
或
棒球,足球和篮球
只有a和c人会出现
我想要的是所有3人出现,因为如果我选择所有体育运动,那么b有足球吗? 。
非常不确定我需要采取哪些步骤以及我需要做出的改变才能实现这一目标。
感谢您的帮助。
sql split功能:
ALTER 功能 fnSplitter( @IDs Varchar ( 100 ))
返回 @ Tbl_IDs 表(ID Int ) As
开始
< span class =code-comment> - 附加逗号
设置 @ IDs = @ IDs + ' < span class =code-string>,'
- 保持位置的索引搜索
声明 @ Pos1 Int
声明 @ pos2 Int
- 从第一个字符
设置 @Pos1 = 1
设置 @ Pos2 = 1
@ Pos1< Len( @ID )
开始
设置 @ Pos1 = CharIndex(' ,', @ IDs , @ Pos1 )
插入 @ Tbl_IDs 选择 Cast(子串( @ID , @ Pos2 ,@ Pos1- @ Pos2) As Int )
<跨度lass =code-comment> - 转到下一个非逗号字符
设置 @ Pos2 = @ Pos1 + 1
- 从下一个charcater搜索
设置 @ Pos1 = @ Pos1 + 1
结束
返回
结束
dataset / tableadapter查询
SELECT DISTINCT
person.personID,sports.sportsID,person.personName,sports.sport,
FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID
WHERE (person.personName LIKE ' %' + @ personName + ' %')
AND
(sports.sportID IN
( SELECT ID FROM dbo.fnSplitter ( @ IDs ) AS fnSplitter_1) OR @ IDs = 0 )
/ * @IDs = 0是初始值...在页面加载时弹出eveerything * /
html中的
,我有一个gridview,在objectdatasource中删除,select参数是发送到@的复选框列表ID来自拆分函数,类型是字符串
你甚至不需要一个函数。由于您要传递逗号分隔值,因此您需要进行动态查询。例如,声明一个SQL变量,然后将该变量设置为select语句。之后执行该变量。
DECLARE @ SQL VARCHAR ( 1000 )
SET @ SQL =
' SELECT DISTINCT
person.personID,sports.sportsID,person.personName,sports.sport,
FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID
WHERE(person.personName LIKE''%' + @ personName + ' %'')
AND
(spor ts.sportID IN(@IDs))'
EXEC ( @ SQL 跨度>)
i''m using a checkbox list as a filter for a sql query. i pass the values as a csv and so far...so almost good.
the problem is, if i select more than one value, then i will ignore/skip over records that do NOT have AT LEAST (not sure how else to put it) all of the selected values.
ex...
(dashes were for formatting purposes only)
person -------person/sport---------sport
_________________________________________
a ------------a | baseball------baseball
b ------------a | football------football
c ------------a | baseball------basketball
--------------b | baseball
--------------b | football
--------------c | baseball
--------------c | football
--------------c | basketball
if i select "baseball" then person a, b, and c will be returned.
if i select baseball and football then all 3 persons will show up in the gridview.
however, if i select any combination with baseketball, then b will NOT show up because in the many-many table, b does not have basketball.
for instance, if i pick football and basketball
or
baseball, football, and basketball
only persons a and c will show up
what i would like is for all 3 to show up because person b DOES have football if i select all sports.
very unsure what steps i need to take and changes i need to make in order for that to happen.
thanks for the help.
sql split function:
ALTER FUNCTION fnSplitter (@IDs Varchar(100) )
Returns @Tbl_IDs Table (ID Int) As
Begin
-- Append comma
Set @IDs = @IDs + ','
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
-- Start from first character
Set @Pos1=1
Set @Pos2=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(',',@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
-- Go to next non comma character
Set @Pos2=@Pos1+1
-- Search from the next charcater
Set @Pos1 = @Pos1+1
End
Return
End
dataset/tableadapter query
SELECT DISTINCT
person.personID, sports.sportsID, person.personName, sports.sport,
FROM sports INNER JOIN
person INNER JOIN
personSport ON personSport.personID = person.personID
INNER JOIN
personSport ON personSport.sportID = sports.sportsID
WHERE (person.personName LIKE '%' + @personName + '%')
AND
(sports.sportID IN
(SELECT ID FROM dbo.fnSplitter(@IDs) AS fnSplitter_1) OR @IDs = 0)
/*the @IDs = 0 is for the initial value...eveerything pops up on pageload*/
in the html, i have a gridview, dropped in an objectdatasource and the select parameter is the checkboxlist which sends to "@IDs" from the split function and the type is "string"
You don''t even need a function. Since you are passing comma separated values, you will need to make a dynamic query. For instance, declare a SQL variable, then set that variable to your select statement. After that execute that variable.
DECLARE @SQL VARCHAR(1000) SET @SQL = 'SELECT DISTINCT person.personID, sports.sportsID, person.personName, sports.sport, FROM sports INNER JOIN person INNER JOIN personSport ON personSport.personID = person.personID INNER JOIN personSport ON personSport.sportID = sports.sportsID WHERE (person.personName LIKE ''%' + @personName + '%'') AND (sports.sportID IN(@IDs))' EXEC(@SQL)
这篇关于逗号分隔的字符串过滤参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!