多表联合查询sql存储过程带分页

来源：互联网发布：linux查看硬盘个数编辑：程序博客网时间：2024/05/20 18:45

note：分页可能不是特别好，把搜索的结果插入到临时表里面，然后再进行分页，感觉速度会慢，但是不知道怎么解决

sql代码：

USE [opussys_db]GO/****** Object:  StoredProcedure [dbo].[proc_DataSearch]    Script Date: 2015-03-04 14:52:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Mofijeck>-- Create date: <2015-03-04>-- Description:<三张表联合搜索>-- =============================================ALTER PROCEDURE [dbo].[proc_DataSearch]-- Add the parameters for the stored procedure here(@words nvarchar(200) ,@type int ,@startIndex int ,@endIndex int)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.create table #Tmp  --创建临时表#Tmp    (      ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1  searchid int,        updateTime datetime,    stype int,      primary key (ID)      --定义ID为临时表#Tmp的主键          )truncate table #Tmpif @type=0begin--新闻insert into #Tmp select id,creattime,1 from table1 where col like '%'+rtrim(@words)+'%'--end--作家insert into #Tmpselect id,creattime,2 from table2 where col like '%'+rtrim(@words)+'%'--end--作品insert into #Tmpselect id,creattime,3 from table3 where col like '%'+rtrim(@words)+'%'    --endendif @type=1begin--新闻insert into #Tmp select id,creattime,@type from table1 where col like '%'+rtrim(@words)+'%'endif @type=2begin--作家insert into #Tmpselect id,creatTime,@type from table2 where col like '%'+rtrim(@words)+'%'endif @type=3begin--作品insert into #Tmpselect id,creattime,@type from table3 where col like '%'+rtrim(@words)+'%'end--返回结果SELECT * FROM (   SELECT ROW_NUMBER() OVER (      order by T.updateTime desc   ) AS Row, T.*  from #Tmp T) as TT where TT.Row between  @startIndex and @endIndex--计算记录数    declare @count int    select @count=count(ID) from #Tmp    return @count    -- Insert statements for procedure hereEND

C#获取返回结果值和结果集：

#region        /// <summary>        ///         /// </summary>        /// <param name="words">关键词 </param>        /// <param name="type"></param>        /// <returns></returns>        public static DataSet searchByWords(string words, int type,int startIndex,int endIndex) {             IDataParameter[] parameter ={                                            new SqlParameter("@words",SqlDbType.NVarChar,200),                                            new SqlParameter("@type",SqlDbType.Int,4),                                            new SqlParameter("@startIndex",SqlDbType.Int,4),                                            new SqlParameter("@endIndex",SqlDbType.Int,4)                                       };            parameter[0].Value = words;            parameter[1].Value = type;            parameter[2].Value = startIndex;            parameter[3].Value = endIndex;            DataSet ds = DbHelperSQL.RunProcedure("proc_DataSearch", parameter, "search");            return ds;        }        /// <summary>        /// 返回结果        /// </summary>        /// <param name="words"></param>        /// <param name="type"></param>        /// <returns></returns>        public static int searchByWordsCount(string words, int type, int startIndex, int endIndex)        {            IDataParameter[] parameter ={                                            new SqlParameter("@words",SqlDbType.NVarChar,200),                                            new SqlParameter("@type",SqlDbType.Int,4),                                            new SqlParameter("@startIndex",SqlDbType.Int,4),                                            new SqlParameter("@endIndex",SqlDbType.Int,4)                                       };            parameter[0].Value = words;            parameter[1].Value = type;            parameter[2].Value = startIndex;            parameter[3].Value = endIndex;            int results = 0;            int returnCount = DbHelperSQL.RunProcedure("proc_DataSearch", parameter, out results);            return returnCount;        }        #endregion

0 0