前两天在做一个新闻模块的时候遇到了点困难 ,就是多条件查询该怎么做。后来经过想他人请教终于把问题解决了。现在来和大家分享一下。
表设计如下:
存储过程如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Hongyu Niu>
-- Create date: <2007-7-21>
-- Description: <多条件查询新闻>
-- Return Value:
-- =============================================
ALTER PROCEDURE [dbo].[P_News_ContentGetAll]
@StrFilter varchar(200) --查询的过滤条件
AS
declare @Str varchar (1000)
set @Str = 'SELECT dbo.T_News_Content.NewsID, dbo.T_News_Content.NewsTitle,
dbo.T_News_Content.[Content], dbo.T_News_Content.InDate,
dbo.T_News_Content.UserID, dbo.T_News_Content.TypeID,
dbo.T_News_Type.TypeName
FROM dbo.T_News_Content left JOIN dbo.T_News_Type
ON dbo.T_News_Content.TypeID = dbo.T_News_Type.TypeID where ' + @StrFilter
Exec(@str)
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Hongyu Niu>
-- Create date: <2007-7-21>
-- Description: <多条件查询新闻>
-- Return Value:
-- =============================================
ALTER PROCEDURE [dbo].[P_News_ContentGetAll]
@StrFilter varchar(200) --查询的过滤条件
AS
declare @Str varchar (1000)
set @Str = 'SELECT dbo.T_News_Content.NewsID, dbo.T_News_Content.NewsTitle,
dbo.T_News_Content.[Content], dbo.T_News_Content.InDate,
dbo.T_News_Content.UserID, dbo.T_News_Content.TypeID,
dbo.T_News_Type.TypeName
FROM dbo.T_News_Content left JOIN dbo.T_News_Type
ON dbo.T_News_Content.TypeID = dbo.T_News_Type.TypeID where ' + @StrFilter
Exec(@str)
如此一来只需要传入一个查询的条件就OK了 呵呵
在我的项目中生成查询条件的代码如下:
private string GetFilterString()
{
string str = " NewsID > 0 ";
if (ddlNewsType.SelectedIndex > 0)
{
str += "and (dbo.T_News_Content.TypeID = " + ddlNewsType.SelectedValue + " ) ";
}
if (txtNewsTitle.Text.Trim() != "" && !string.IsNullOrEmpty(txtNewsTitle.Text))
str += "and NewsTitle like '%%" + txtNewsTitle.Text.Trim() + "%%'" ;
if (txtInDate.Text.Trim() != "" && !string.IsNullOrEmpty(txtInDate.Text))
{
string strBegin = cldInDate.SelectedDate.ToShortDateString();
string strEnd = cldInDate.SelectedDate.AddDays(1).ToShortDateString();
str += "and InDate < '" + strEnd + "' ";
str += "and InDate > '" + strBegin + "' ";
}
str += "and UserID = '" + Client.UserID + "'";
return str;
}
{
string str = " NewsID > 0 ";
if (ddlNewsType.SelectedIndex > 0)
{
str += "and (dbo.T_News_Content.TypeID = " + ddlNewsType.SelectedValue + " ) ";
}
if (txtNewsTitle.Text.Trim() != "" && !string.IsNullOrEmpty(txtNewsTitle.Text))
str += "and NewsTitle like '%%" + txtNewsTitle.Text.Trim() + "%%'" ;
if (txtInDate.Text.Trim() != "" && !string.IsNullOrEmpty(txtInDate.Text))
{
string strBegin = cldInDate.SelectedDate.ToShortDateString();
string strEnd = cldInDate.SelectedDate.AddDays(1).ToShortDateString();
str += "and InDate < '" + strEnd + "' ";
str += "and InDate > '" + strBegin + "' ";
}
str += "and UserID = '" + Client.UserID + "'";
return str;
}
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1716658