关注我
具体实现可以参考下面的具体代码：
CREATE PROCEDURE [dbo].[usp_GetArticleByCategoryIDAndArticleLabel]
-- Add the parameters for the stored procedure here
@categoryID int=-1,
@selectContent nvarchar(100),
@bExactSelect bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @ch as nvarchar(100),@StrSeprate nvarchar(2)
DECLARE @sql nvarchar(4000),@bFirst bit=0
set @StrSeprate=','
set @selectContent=@selectContent+@StrSeprate
if (@categoryID = -1)
begin
if (@bExactSelect = 0)
begin
set @sql='SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where 1=1'
while(LEN(LTRIM(RTRIM(@selectContent))) <> 0)
begin
set @ch=left(@selectContent,charindex(',',@selectContent,1)-1)
if(@bFirst=0)
begin
set @bFirst=1
set @sql= @sql + ' and (ArticleLabel like ''%' + @ch + '%'''
end
else
begin
set @sql= @sql + ' or ArticleLabel like ''%' + @ch + '%'''
end
set @selectContent=stuff(@selectContent,1,charindex(',',@selectContent,1),'')
end
if(@bFirst=1)
set @sql=@sql+') order by CreatedTime desc'
end
else
begin
set @sql='SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where 1=1'
while(LEN(LTRIM(RTRIM(@selectContent))) <> 0)
begin
set @ch=left(@selectContent,charindex(',',@selectContent,1)-1)
set @sql= @sql + ' and ArticleLabel like ''%' + @ch + '%'''
set @selectContent=stuff(@selectContent,1,charindex(',',@selectContent,1),'')
end
set @sql=@sql+' order by CreatedTime desc';
end
end
else
begin
if (@bExactSelect = 0)
begin
set @sql='SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID='+cast(@categoryID as varchar)
while(LEN(LTRIM(RTRIM(@selectContent))) <> 0)
begin
set @ch=left(@selectContent,charindex(',',@selectContent,1)-1)
if(@bFirst=0)
begin
set @bFirst=1
set @sql= @sql + ' and (ArticleLabel like ''%' + @ch + '%'''
end
else
begin
set @sql= @sql + ' or ArticleLabel like ''%' + @ch + '%'''
end
set @selectContent=stuff(@selectContent,1,charindex(',',@selectContent,1),'')
end
if(@bFirst=1)
set @sql=@sql+') order by CreatedTime desc'
end
else
begin
set @sql='SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID='+cast(@categoryID as varchar)
while(LEN(LTRIM(RTRIM(@selectContent))) <> 0)
begin
set @ch=left(@selectContent,charindex(',',@selectContent,1)-1)
set @sql= @sql + ' and ArticleLabel like ''%' + @ch + '%'''
set @selectContent=stuff(@selectContent,1,charindex(',',@selectContent,1),'')
end
set @sql=@sql+' order by CreatedTime desc';
end
end
exec sp_executesql @sql
END
GO在SQL Server中实现字符串的分割模糊查询从以上代码中可以看出是比较麻烦的，如果使用下面的存储过程来实现则比较简单，把对字符串的分割放到C#语言中用split分割函数进行处理，具体代码如下：
CREATE PROCEDURE [dbo].[usp_GetArticlePagerData]
-- Add the parameters for the stored procedure here
--@tblName varchar(255), -- 表名
@strGetFields nvarchar(1000) = '*', -- 需要返回的列，默认*
@strOrder nvarchar(255)='', -- 排序的字段名，必填
@strOrderType nvarchar(10)='ASC', -- 排序的方式,默认ASC
@strWhere nvarchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@pageSize int = 10, -- 页尺寸,默认10
@pageIndex int = 1 -- 页码,默认1
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @strSQL nvarchar(4000)='',@insideWhere nvarchar(1000)='',@outsideWhere nvarchar(1000)=''
if @strWhere != ''
set @outsideWhere=' where '+@strWhere
if @strWhere != ''
set @insideWhere=' and '+@strWhere
if(@pageIndex>1)
if(@strOrderType != 'asc')
set @strSQL=
'SELECT top('+str(@pageSize)+') '+@strGetFields+' FROM Article WHERE ' + @strOrder + ' < (select MIN(' + @strOrder + ') from ' +
'(select top('+str((@pageIndex-1)*@pageSize)+') '+@strOrder+' from Article'+@outsideWhere+' order by '+@strOrder+
' desc) as T) ' + @insideWhere + ' order by '+@strOrder+' desc'
else
set @strSQL=
'SELECT top('+str(@pageSize)+') '+@strGetFields+' FROM Article WHERE ' + @strOrder + ' > (select MAX(' + @strOrder + ') from ' +
'(select top('+str((@pageIndex-1)*@pageSize)+') '+@strOrder+' from Article'+@outsideWhere+' order by '+@strOrder+
' asc) as T) ' + @insideWhere + ' order by '+@strOrder+' asc'
else
set @strSQL=
'SELECT top('+str(@pageSize)+') '+@strGetFields+' FROM Article' + @outsideWhere + ' order by '+@strOrder+' '+@strOrderType
--print @strSql exec usp_GetArticlePagerData 'ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks','CreatedTime','asc','ArticleID like ''%4%''',2,2
exec sp_executesql @strSQL
END
GO在C#中对字符串的分割拼接SQL的查询条件如下：
/// <summary>
/// 根据文章关键词、文章类别、是否模糊来得到查询文章信息的条件
/// </summary>
/// <param name="articleLabel">文章关键词，可以为多个关键词进行查询</param>
/// <param name="categoryID">文章类别编号</param>
/// <param name="bExactSearch">对文章关键词是否进行模糊查询，精确：关键词之间并且；模糊：关键词之间或者</param>
/// <returns>查询条件字符串</returns>
private static string GetSearchWhereByArticleLabelAndCategoryID(string articleLabel, int categoryID, bool bExactSearch)
{
string strWhere = string.Empty;
if (categoryID != -1)
{
strWhere += "categoryID=" + categoryID;
}
string[] arrLabel = articleLabel.Replace('，', ',').Split(',');
if (arrLabel.Length > 0)
{
if (!string.IsNullOrEmpty(strWhere))
{
strWhere += " and ";
}
strWhere += "(ArticleLabel like '%" + arrLabel[0] + "%'";
if (bExactSearch)
{
for (int i = 1; i < arrLabel.Length; i++)
{
strWhere += " and ArticleLabel like '%" + arrLabel[i] + "%'";
}
}
else
{
for (int i = 1; i < arrLabel.Length; i++)
{
strWhere += " or ArticleLabel like '%" + arrLabel[i] + "%'";
}
}
strWhere += ")";
}
return strWhere;
}
/// <summary>
/// 得到查询的文章信息
/// </summary>
/// <param name="articleLabel">文章关键词，可以为多个关键词进行查询</param>
/// <param name="categoryID">文章类别编号</param>
/// <param name="bExactSearch">对文章关键词是否进行模糊查询，精确：关键词之间并且；模糊：关键词之间或者</param>
/// <param name="pageSize">分页大小</param>
/// <param name="pageIndex">当前页的索引</param>
/// <returns>返回查询到的文章信息</returns>
public static IList<Article> GetArticleByArticleLabelAndCategoryID(string articleLabel, int categoryID, bool bExactSearch, int pageSize, long pageIndex)
{
string strWhere = GetSearchWhereByArticleLabelAndCategoryID(articleLabel, categoryID, bExactSearch);
return ArticleService.GetArticlePagerData("ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks", "CreatedTime", "desc", strWhere, pageSize, pageIndex);
}
/// <summary>
/// 获取分页查询的文章信息
/// </summary>
/// <param name="strGetFields">要查询的列的信息，即需要返回的列</param>
/// <param name="strOrder">排序的字段名</param>
/// <param name="strOrderType">排序的方式，升序ASC，降序DESC</param>
/// <param name="strWhere">查询条件 (注意: 不要加 where)</param>
/// <param name="pageSize">页尺寸,即页面的记录数</param>
/// <param name="pageIndex">页码，即当前获取数据的页面号</param>
/// <returns></returns>
public static IList<Article> GetArticlePagerData(string strGetFields, string strOrder, string strOrderType, string strWhere, int pageSize, long pageIndex)
{
IList<Article> articleList = new List<Article>();
Article article = null;
SqlParameter[] para = new SqlParameter[]{
new SqlParameter("@strGetFields", strGetFields),
new SqlParameter("@strOrder", strOrder),
new SqlParameter("@strOrderType", strOrderType),
new SqlParameter("@strWhere", strWhere),
new SqlParameter("@pageSize", pageSize),
new SqlParameter("@pageIndex", pageIndex)
};
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.connectionString, "dbo.usp_GetArticlePagerData", para))
{
while (reader.Read())
{
article = new Article();
article.ArticleID = Convert.ToInt64(reader["ArticleID"]);
article.Title = reader["Title"].ToString();
article.ArticleLabel = reader["ArticleLabel"].ToString();
article.CreatedTime = reader["CreatedTime"].ToString();
article.Clicks = Convert.ToInt32(reader["Clicks"]);
articleList.Add(article);
}
}
return articleList;
}下面的分页存储过程也可以实现，但是不如上面的分页存储过程高效：
CREATE PROCEDURE [dbo].[usp_ShowPage]
-- Add the parameters for the stored procedure here
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='', -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认10
@PageIndex int = 1, -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @strSQL varchar(5000)
if @strWhere !=''
set @strWhere=' where '+@strWhere
set @strSQL=
'SELECT * FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strGetFields+' '+
'FROM ['+@tblName+'] '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
exec (@strSQL)
END