具体实现可以参考下面的具体代码:
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