sql笔记

ALTER  procedure [dbo].[A_DBManageList_ADD]
@UserName varchar(20),
@PassWord varchar(20),
@tableName varchar(20),
@CreateTime datetime
 AS 
 declare @newTable varchar(2000), @id int,@i int,@SysName varchar(8000)
 begin
 set XACT_ABORT on
 begin tran
    --插入A_DBManageList
    insert into A_DBManageList(UserName,PassWord,IsActive,CreateTime,GroupCode,AccectsCount,CustomerCount,VehicleCount)values(@UserName,@PassWord,1,@CreateTime,'0,1,2,3,4,5','1','0','0')	
    
    --插入子表S_SysUser
    set @newTable='insert into '+@tableName+'.dbo.S_SysUser([UserName],[Password],IsActive,GroupCode)select a.UserName,a.PassWord,a.IsActive,a.GroupCode from DB_TFDB.dbo.A_DBManageList a where a.UserName=''' + @UserName + ''' and a.PassWord='''+@PassWord+''''  
    exec(@newTable)
    --print @newTable
    
    --获取上次插入A_DBManageList ID值
    select @id=@@identity
    select @i=0
    while  @i<=4
		begin
		  set @SysName='insert into '+@tableName+'.dbo.S_UserRole(RoleID,UserID) values('+convert(nvarchar(20),@i)+','+convert(nvarchar(20),@id)+')' 
		  exec(@SysName)
		  set @i=@i+1
		end
    commit tran   
end

ALTER PROCEDURE [dbo].[A_DBManageList_Coutnt]
 AS 
 begin
    declare @CountUser varchar(200), @CountCustomer varchar(200),@CountVehicle varchar(200),@Sum int,@temp varchar(2000),@UserName varchar(20),
            @TableUser varchar(200), @TableCustomer varchar(200), @TableDriver varchar(200)
       declare cr_cursor cursor --定义游标
       for select count(1)as result , UserName  from A_DBManageList a group by UserName
       open cr_cursor --打开游标
       fetch From cr_cursor into @Sum,@UserName --提取游标
         while @@fetch_status=0
           begin
                 --统计S_SysUser
                 set @TableUser='select count(1) from  TFDB_'+@UserName+'.dbo.S_SysUser'  
                 --exec(@TableUser)
                 
                 --统计A_CustomerInfo
                 set @TableCustomer='select count(1) from  TFDB_'+@UserName+'.dbo.A_CustomerInfo'  
                 --exec(@TableCustomer)
                 
                 --统计A_DriverInfo
                 set @TableDriver='select count(1) from  TFDB_'+@UserName+'.dbo.A_DriverInfo'  
                 --exec(@TableDriver)
                 
                 --更新A_DBManageList
                 set @temp='update A_DBManageList set AccectsCount=('+@TableUser+'),CustomerCount=('+@TableCustomer+'),VehicleCount=('+@TableDriver +') where UserName='''+@UserName+''' '
                 exec (@temp)
                 print (@temp)
                 fetch next From cr_cursor into @Sum,@UserName
           end;
       close cr_cursor --关闭游标
       deallocate cr_cursor --释放游标
 end

ALTER PROCEDURE [dbo].[A_DBManageList_Delete]
@ListID int,
@TableName varchar(20)
 AS 
begin
    declare @kid   varchar(200),@dropBase varchar(200)
       
    --删除A_DBManageList
    DELETE A_DBManageList WHERE  ListID=@ListID 
    
    set  @kid= ' ' 
    select  @kid=@kid+ ' kill '+cast(spid as varchar(8)) from   master..sysprocesses 
    where dbid=db_id(@TableName) 
    Exec(@kid)
    --print(@kid)
    
    --删除子库
    set @dropBase='drop database '+ @TableName+''
    Exec(@dropBase)
    --print(@dropBase)
    
end

ALTER PROCEDURE [dbo].[A_DBManageList_Exists]
@UserName varchar(20),
@PassWord varchar(20),
@DBTableName varchar(20),
@TableName varchar(20)
AS
begin   
--首先判断用户输入的用户名对应数据库是否存在反之  返回0
--查询@DBTableName中心库利用COUNT(1)记录数据是否存在
--同理@TableName 最后用UNION  ALL 并上这个数据集合的结果
--最后利用CASE WHEN xx THEN 来判断结果是否等于2如果是 返回 1反之0 

  if exists (select * from sys.databases where name =@TableName)  
       exec('SELECT  ( CASE WHEN SUM(B.usercont) = 2 THEN 1
                       ELSE 0
                  END ) result
        FROM    ( SELECT    COUNT(1) usercont
                  FROM      '+@DBTableName+' a1
                  WHERE     a1.UserName ='''+@UserName+'''
                            AND a1.PassWord ='''+@PassWord+'''
                  UNION  ALL
                  SELECT    COUNT(1) usercont
                  FROM      '+@TableName+'.dbo.S_SysUser a2
                  WHERE     a2.UserName = '''+@UserName+'''
                            AND a2.Password = '''+@PassWord+'''
                ) B')
  else --永不成立的条件返回0
        select count(1) usercont from  S_SysUser a2 where  1=2    
end

ALTER PROCEDURE [dbo].[A_DBManageList_Update]
@ListID      int,
@UserName    varchar(20),
@PassWord    varchar(20),
@CreateTime  datetime,
@TableName   varchar(20),
@SysName     varchar(20),
@SysPwd     varchar(20)
 AS 
begin 
    set XACT_ABORT on
    begin tran
       declare @newTable varchar(200), @kid varchar(200) 
  
       --实现数据更新A_DBManageList
       UPDATE [A_DBManageList] SET [PassWord] = @PassWord,[CreateTime] = @CreateTime,[UserName]=@UserName WHERE  ListID=@ListID  
   
       --实现数据更新S_SysUser   
       set @newTable='UPDATE '+@TableName+'.dbo.S_SysUser SET [Password] = '''+@PassWord +''',[CreateTime]='''+convert(varchar(20),@CreateTime)+''',[UserName]='''+@UserName+'''
                     where  UserName=''' + @SysName + ''' and Password=''' + @SysPwd + ''' '
       exec(@newTable)
       commit tran      
end

--实现中心库 子库同步
--date    :2012-9-1
ALTER PROCEDURE [dbo].[S_DB_TFDB_ADD]
@UserName varchar(20),
@Password varchar(20),
@GroupCode varchar(50),
@RoleID varchar(20),
@tableName varchar(20)
 AS 
 begin
 declare @ChildTableSysUser varchar(2000),@ChildTableUserRole varchar(2000),@Table varchar(200),@id int,@i int
   set xact_abort on 
   begin tran
     --插入子表S_SysUser
     set @ChildTableSysUser='insert into '+@tableName+'.dbo.S_SysUser(UserName,Password,GroupCode)values('''+@UserName+''','''+@Password+''','''+@GroupCode+''')'  
     --print @ChildTableSysUser
     exec (@ChildTableSysUser) 
     
     --获取上次插入S_SysUserID
     select @id=@@identity;
     --print @id
     
     --插入S_SysUser_Child
     insert into S_SysUser_Child(UserID,UserName,Password,GroupCode,RoleID) values(@id,@UserName,@Password,@GroupCode,@RoleID) 

     declare cr_cursor cursor --定义游标
     for select * from dbo.f_split(@RoleID,',')
     open cr_cursor --打开游标
     fetch From cr_cursor into @Table --提取游标
     while @@fetch_status=0
         begin
             set @ChildTableUserRole='insert into '+@tableName+'.dbo.S_UserRole(UserID,RoleID) values('+convert(nvarchar(20),@id)+','+@Table+')' 
             --print @ChildTableUserRole 
             exec(@ChildTableUserRole)
             fetch next From cr_cursor into @Table
         end;
     close cr_cursor --关闭游标
     deallocate cr_cursor --释放游标
     
   commit  tran    
end

1,2,3,4(实现split功能的函数)

ALTER function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2012-9-1
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>'' 
       insert @temp values(@SourceSql)
    return 
end

CREATE FUNCTION [dbo].[func_Split](
    @Value       [NVARCHAR](4000),
    @Separator   [NVARCHAR](10),
    @RemoveEmpty [BIT]
)
RETURNS @T TABLE(
    [Index] [INT] NOT NULL,
    [Value] [NVARCHAR](4000)
)
AS
BEGIN
    DECLARE @Index [INT]
    DECLARE @Start [INT]
    DECLARE @L     [INT]
    DECLARE @Temp  [NVARCHAR](4000)

    SELECT @Start = 1
    SELECT @L = LEN(@Separator)

    WHILE(1 = 1)
    BEGIN
        SELECT @Index = CHARINDEX(@Separator, @Value, @Start)
        IF(@Index > 0)
        BEGIN
            SELECT @Temp = SUBSTRING(@Value, @Start, @Index - @Start)
            IF(N'' <> @Temp OR 0 = @RemoveEmpty)
                INSERT INTO @T([Index], [Value]) SELECT @Start, @Temp
            SELECT @Start = @Index + @L
        END
        ELSE
        BEGIN
            IF(@Start <= LEN(@Value))
            BEGIN
                SELECT @Temp = SUBSTRING(@Value, @Start, LEN(@Value) - @Start + 1)
                INSERT INTO @T([Index], [Value]) SELECT @Start, @Temp
            END
            ELSE
                IF(0 = @RemoveEmpty)
                    INSERT INTO @T([Index], [Value]) SELECT @Start, N''
            BREAK
        END
    END

    RETURN
END;
GO

SELECT * FROM [dbo].[func_Split]('192.168.5.12', '.', 1);

DROP FUNCTION  [dbo].[func_Split];
GO

sql作业类似实现触发器一样的过程(事先建立好存储过程过程 sql )

查询CPU占用高的语句

SELECT TOP 10
   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,
   execution_count,
   (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
         THEN LEN(CONVERT(nvarchar(max), text)) * 2
         ELSE statement_end_offset
      END - statement_start_offset)/2)
   FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [avg_cpu_cost] DESC

查询缺失索引

SELECT 
    DatabaseName = DB_NAME(database_id)
    ,[Number Indexes Missing] = count(*) 
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY 2 DESC;

SELECT  TOP 10 
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns 
        , [InequalityUsage] = inequality_columns
        , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g 
INNER JOIN    sys.dm_db_missing_index_group_stats s 
       ON s.group_handle = g.index_group_handle 
INNER JOIN    sys.dm_db_missing_index_details d 
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

查看sql文件路径

--drop table #dbfiles
--deallocate cursor1
 
------
declare cursor1 cursor for
    SELECT name from sys.databases
 
-- 临时表  www.2cto.com  
CREATE TABLE #dbfiles
( 
dbname nvarchar(100),
filetype nvarchar(100),
filename nvarchar(200)
)
 
declare @name  varchar(100) --数据库名
declare @sql nvarchar(1024) --临时sql 语句
 
open cursor1
 
fetch next from cursor1 into @name
 
while @@fetch_status = 0
begin  
    set @sql = 'INSERT INTO #dbfiles SELECT ''' + @name + ''',name,filename  from ' + @name + '.sys.sysfiles';
    EXEC(@sql)
 
    fetch next from cursor1 into @name 
end
 
close cursor1                   --关闭游标
deallocate cursor1
 
select * from #dbfiles
 
drop table #dbfiles --删除临时文件

sql 查看日志文件大小

create table dbo.LogSize  
(  
dbname nvarchar(50) not null  
,logsize decimal(8,2) not null  
,logused decimal(5,2) not null  
,status int null  
)  
insert into dbo.LogSize  
execute('dbcc sqlperf(logspace) with no_infomsgs') 

select * from LogSize

或者直接 execute('dbcc sqlperf(logspace) with no_infomsgs')

sql server性能分析--执行sql次数和逻辑次数

先以下命令清除sql server的缓存：

dbcc freeProcCache

SELECT  creation_time  N'语句编译时间'

        ,last_execution_time  N'上次执行时间'

        ,total_physical_reads N'物理读取总次数'

        ,total_logical_reads/execution_count N'每次逻辑读次数'

        ,total_logical_reads  N'逻辑读取总次数'

        ,total_logical_writes N'逻辑写入总次数'

        , execution_count  N'执行次数'

        , total_worker_time/1000 N'所用的CPU总时间ms'

        , total_elapsed_time/1000  N'总花费时间ms'

        , (total_elapsed_time / execution_count)/1000  N'平均时间ms'

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset 

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END 

            - qs.statement_start_offset)/2) + 1) N'执行语句'

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset 

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END 

            - qs.statement_start_offset)/2) + 1) not like '%fetch%'

ORDER BY  total_elapsed_time / execution_count DESC;

--查看4小时内的CPU变化值，1分钟统计一次

declare @ts_now bigint

select @ts_now = ms_ticks from sys.dm_os_sys_info

--select * from sys.dm_os_sys_info

select record_id,

dateadd(ms, convert(bigint,-1) * (@ts_now - [timestamp]), GetDate()) as EventTime,

SQLProcessUtilization,

SystemIdle,

100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization

from (

select

record.value('(./Record/@id)[1]', 'int') as record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,

timestamp

from (

select timestamp, convert(xml, record) as record

from sys.dm_os_ring_buffers

where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

and record like '%<SystemHealth>%') as x

) as y

order by record_id desc

--查看磁盘空间大小

create table #a (id int IDENTITY(1,1),DiskName varchar(50))

insert into #a(DiskName)

exec xp_cmdshell 'wmic LOGICALDISK get name'

create table #b (id int IDENTITY(1,1),freespace varchar(50))

insert into #b(freespace)

exec xp_cmdshell 'wmic LOGICALDISK get freespace'

create table #c (id int IDENTITY(1,1),size varchar(50))

insert into #c(size)

exec xp_cmdshell 'wmic LOGICALDISK get size'

select server_name=@@servername,DiskName

,convert(bigint,replace(size,char(13),''))/1024/1024/1024 as total_disk_size_gb

,convert(bigint,replace(#b.freespace,char(13),''))/1024/1024/1024 as free_disk_size_gb

,convert(varchar,convert(decimal(4, 2),(convert(decimal(15, 2),convert(decimal(15, 2),replace(#b.freespace,char(13),''))/1024/1024/1024*100)/

convert(decimal(15, 2),convert(decimal(15, 2),replace(size,char(13),''))/1024/1024/1024))))+'%' as free_space_percent

from #a join #b on #a.id=#b.id join #c on #a.id=#c.id

where #a.id >1 and #b.freespace is not null and charindex(char(13),replace(#b.freespace,' ','')) <>1

drop table #a,#b,#c

sql 自动创建数据库 (一个月创建一次 sql新建作用)

/*******************************************************
*    Remark:            自动创建数据库

*******************************************************/
--    exec SP_AutoCreateHistoryDataBase

ALTER PROCEDURE [dbo].[SP_AutoCreateHistoryDataBase]

AS
begin
  
    declare @dbname varchar(20),
        @NextMonth datetime,
        @Yeah varchar(4),
        @Month varchar(2)

    set @NextMonth = dateadd(month,1,getDate())
    set @Yeah = cast(year(@NextMonth) as varchar(4))
    select @Month = case when month(@NextMonth) >= 10 then cast(month(@NextMonth) as varchar(2)) else '0' + cast(month(@NextMonth) as varchar(2)) end
    set @dbname='sp' + @Yeah + @Month

    declare @sql varchar(5000)
    set @sql ='
            IF NOT EXISTS(select * from sys.databases where name = ''' + @dbname +''')
            BEGIN
                CREATE DATABASE ' + @dbname + ' ON PRIMARY
                (
                    NAME = N''' + @dbname + ''',FILENAME = N''D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\'+@dbname + '.mdf'',SIZE = 3072KB,FILEGROWTH = 1024KB
                )
                LOG ON
                (
                    NAME = N''' + @dbname + '_log'',FILENAME = N''D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\'+@dbname + '_log.ldf'',SIZE = 1024KB,MAXSIZE = 2048MB,FILEGROWTH = 10240KB
                )
            END
    '

    exec (@sql)    

end

exec SP_AutoCreateHistoryDataBase

表结构复制跨服务器

select * into 新表 from opendatasource('SQLOLEDB','Data Source=远程数据库IP;User ID=用户名; Password=密码').远程数据库名称.dbo.远程数据库表

提示：

SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。

代码：

exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

使用完成后，关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

跨服务器查询

--前提对方服务器执行或者SQL Server 外围应用配置器openrowset/opendatasource(http://www.cnblogs.com/minideas/archive/2010/07/09/1774102.html)

exec sp_configure 'show advanced options',1；
reconfigure；
exec sp_configure 'Ad Hoc Distributed Queries',1；
reconfigure；

SELECT * FROM

OPENDATASOURCE('SQLOLEDB' , 'Data Source=ip;User ID=name;Password=pwd'). 


ITSGPS.dbo .std_UserInfo AS A

select * from OPENDATASOURCE( 'SQLOLEDB', 'Data Source=202.103.191.xxx,1433;User ID=sa;Password=xxxxx' ).UserRealationShip.dbo.T_UserInformation

--新增获取id 并开启事务

            string sql = @"BEGIN TRAN
                           declare @id int;
                             INSERT INTO T_UserInfo(P_UserID,UserName ,Password ,UserType,HoldID,ParentUserID,isDeleted,UpdateTime)
                                          VALUES(@P_UserID,@UserName,@Password,@UserType,@HoldID,@ParentUserID,0,@UpdateTime);
                             select @id=SCOPE_IDENTITY();
                             INSERT INTO T_UserRole(UserID,RoleID)VALUES(@id,@roleID);
                         IF(@@ERROR<>0)
                            BEGIN 
                              ROLLBACK TRAN;
                            END
                         ELSE
                            BEGIN  
                               COMMIT TRAN;
                            END";

View Code

--数据库临时表触发器操作多行

USE [MonthsData201511]
GO
/****** Object:  Trigger [dbo].[ProductOrderDetails_trg]    Script Date: 11/09/2015 17:12:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <lsw> 
-- Create date: <2015-10-23>
-- =============================================
ALTER TRIGGER [dbo].[ProductOrderDetails_trg]
       ON [dbo].[T_ProductOrderDetails]   --将要进行更改的表名
FOR INSERT,UPDATE                         --给表插入一条数据的时候触发
AS
BEGIN
   --删除临时表
   if Object_id('Tempdb..#tempColor')  IS NOT NULL   DROP TABLE #tempColor;
   
   --定义变量
   DECLARE @colorId varchar(5), @ProcID BIGINT,  @colorValue VARCHAR(5);
          
   --插入临时表       
   SELECT * INTO  #tempColor  FROM ( select ColorId ,ProcID from INSERTED) temp;
    
    --赋值并判断
    select @colorId=ColorId,@ProcID=ProcID  from #tempColor
    IF @colorId=27
       SET @colorValue='红色';
    ELSE IF @colorId=28
       SET @colorValue='蓝色';
    ELSE IF @colorId=29
       SET @colorValue='白色';
    ELSE IF @colorId=30
       SET @colorValue='紫色';     
    ELSE IF @colorId=31
       SET @colorValue='绿色';         
    ELSE IF @colorId=32
       SET @colorValue='黄色';
    ELSE IF @colorId=33
       SET @colorValue='蓝色';
    ELSE
       SET @colorValue='未知';
  --更新 通常的触发器网上demo 不支持触发器批量操作
  UPDATE T_ProductOrderDetails SET Color=@colorValue WHERE  ProcID in  (select ProcID from #tempColor)
END

--临时表

USE [MonthsData201511]
GO
/****** Object:  StoredProcedure [dbo].[sp_Ct_ReadShopOrderList]    Script Date: 11/09/2015 17:51:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <lsw> 申明临时表 操作临时表 统计子查询 分页(ROW_NUMBER)
-- Create date: <2015-10-23>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Ct_ReadShopOrderList] 
    @dtBegin datetime,   ----开始时间
    @dtEnd datetime,     ----结束时间
    @search VARCHAR(40), ----订单号 / 商品名称
    @pageIndex int,      ----起始页
    @pageSize  INT,      ----页数,
    @status INT,         --1：待付款 2：待发货 3：待收货 4：待评价 5：已完成 0全部
    @OrderType int       --1：商品订单2：积分商品订单
AS
BEGIN
    SET NOCOUNT ON;
    --申明执行语句sql变量(拼接)
    Declare @SQL nvarchar(max),     @strWhere varchar(15),@strTime varchar(100),@orderBy varchar(100);
    
    Set @SQL=''
    
    --删除临时表
    if Object_id('Tempdb..#temp')  IS NOT NULL   DROP TABLE #temp;
    if Object_id('Tempdb..#ProductOrder')  IS NOT NULL   DROP TABLE #ProductOrder;
    if Object_id('Tempdb..#result')  IS NOT NULL   DROP TABLE #result;

    --组织SQL语句 申明游标 
    Declare my_cursor Cursor For Select dbName,convert(varchar(20),BeginTime,120),convert(varchar(20),EndTime,120) From dbo.GetDataBaseName(@dtBegin,@dtEnd,0)
    open my_cursor
    Declare @DBName Varchar(200), @BeginTime varchar(20),@EndTime varchar(20)
    fetch next from my_cursor into @DBName,@BeginTime,@EndTime
    WHILE(@@fetch_status=0)
    BEGIN
       IF @OrderType=2
        BEGIN 
          if @SQL<>''
            Set @SQL=@SQL + ' Union All '   
            Set @SQL=@SQL+ ' SELECT  a.SendTime,  a.CancelTime,  a.ContactPhone, a.ReceiverAddr, a.ReceiveTime, a.[OrderId],a.[CreateTime],a.[Status],[ReceiverName],[OrderType] ,
                                 (SELECT COUNT(b.OrderId) FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) orderCount,
                                 (SELECT TOP 1 Name FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) name,
                                 (SELECT TOP 1 Integral FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) Integral,
                                 (SELECT SUM(BuyingCount) FROM '+@DBName+'..T_ProductOrderDetails  d WITH(NOLOCK) WHERE d.OrderId=a.OrderId and d.IsDeleted=0) productCount
                                  from '+@DBName+'..[T_ProductOrder]  a  WITH(NOLOCK)' 
             fetch next from my_cursor into @DBName,@BeginTime,@EndTime
           END
       ELSE IF @OrderType=1
        BEGIN 
         if @SQL<>''
           Set @SQL=@SQL + ' Union All '
           Set @SQL=@SQL+ ' SELECT  a.SendTime,  a.CancelTime,  a.ContactPhone, a.ReceiverAddr, a.ReceiveTime, a.[OrderId],a.[CreateTime],a.[Status],[ReceiverName],[OrderType],
                                 (SELECT COUNT(b.OrderId) FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) orderCount,
                                 (SELECT TOP 1 Name FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) name,
                                 (SELECT SUM(BuyingCount) FROM '+@DBName+'..T_ProductOrderDetails  d WITH(NOLOCK) WHERE d.OrderId=a.OrderId and d.IsDeleted=0) productCount
                                 from '+@DBName+'..[T_ProductOrder]  a  WITH(NOLOCK)'                    
                                  
        fetch next from my_cursor into @DBName,@BeginTime,@EndTime
        END
    END
    Close my_cursor
    Deallocate my_cursor
    if IsNull(@SQL,'')<>''
        --@status为0 查询全部            
        SET @strWhere=' and Status='+ Convert(varchar(20),@status)  
        SET @strTime=' CreateTime Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '
                 
        IF @status=0
             select @strWhere='', @orderBy  ='CreateTime';
        ELSE IF @status=1
             select @orderBy  ='CreateTime';
        ELSE IF @status=2
             select @orderBy  ='CreateTime';
        ELSE IF @status=3
             select  @orderBy  ='SendTime';
        ELSE IF @status=4
             select @orderBy  ='CreateTime';
        ELSE IF @status=5
             select @orderBy  ='ReceiveTime', @strTime=' ReceiveTime Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' ' 
        ELSE IF @status=6
             select @orderBy='CancelTime',  @strTime=' CancelTime Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' ' 
            
        SET @SQL='SELECT * INTO  #temp FROM('+@SQL+' ) temp  WHERE OrderType='+Convert(varchar(20),@OrderType)+' '+@strWhere 
        
        --IF @status=0
        --    SET @strWhere=''
        -- ELSE
        --   SET @strWhere=' and Status='+ Convert(varchar(20),@status)  
        --IF @status=5 
        --    SET @strTime=' ReceiveTime Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '
        -- ELSE
        --    SET @strTime=' CreateTime Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '

        Set @SQL=@SQL+';SELECT * INTO  #ProductOrder FROM #temp  WHERE '+@strTime
        
        SET @SQL=@SQL+'; SELECT * INTO  #result FROM(
                                         SELECT *,ROW_NUMBER() OVER(Order by a.['+@orderBy+'] desc ) as row FROM #ProductOrder a 
                                         WHERE a.OrderId LIKE ''%'+@search+'%''  OR a.name LIKE ''%'+@search+'%''
                                       )temp ;
              SELECT COUNT(1) as count FROM #result;
              SELECT * FROM #result  WHERE row between  '+ Convert(varchar(20),@pageIndex)+' and '+ Convert(varchar(20),@pageSize)                
  -- PRINT      @SQL                                                     
   EXEC sp_executesql  @SQL
END


--函数
USE [MonthsData201511]
GO
/****** Object:  UserDefinedFunction [dbo].[GetDataBaseName]    Script Date: 11/09/2015 17:52:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <lsw>
-- =============================================
ALTER FUNCTION [dbo].[GetDataBaseName] 
(
    --开始时间，结束时间，类型(0-业务库，1-历史库)
    @DateBegin datetime,@DateEnd datetime,@Type tinyint
)
RETURNS 
@tempDBName TABLE 
(
    dbName Varchar(200),BeginTime datetime primary key(BeginTime),EndTime datetime 
)
AS
BEGIN
    Declare @datediff int,@i int,@Datetmp datetime,@dbNametmp varchar(200)
    Declare @BeginTimetmp datetime,@EndTimetmp datetime
    Set @i=0
    if(@Type=1)
        BEGIN
            Set @datediff=datediff(day,@DateBegin,@DateEnd)
            While @i<=@datediff
            BEGIN
                Set @Datetmp=dateadd(day,@i,@DateBegin)
                if(@datediff=0)
                    Begin 
                        Set @BeginTimetmp=@DateBegin 
                        Set @EndTimetmp=@DateEnd 
                    End
                else if @i=0
                    Begin 
                        Set @BeginTimetmp=@Datetmp 
                        Set @EndTimetmp=Convert(Varchar(20),@Datetmp,23)+' 23:59:59' 
                    End
                else if @i=@datediff
                    Begin 
                        Set @BeginTimetmp=Convert(Varchar(20),@Datetmp,23)+' 00:00:00' 
                        Set @EndTimetmp=@DateEnd 
                    End
                else
                    Begin 
                        Set @BeginTimetmp=Convert(Varchar(20),@Datetmp,23)+' 00:00:00' 
                        Set @EndTimetmp=Convert(Varchar(20),@Datetmp,23)+' 23:59:59' 
                    End
                Set @dbNametmp='MonthsData'+Convert(varchar(10),year(@Datetmp))+Convert(varchar(10),Month(@Datetmp))+Convert(varchar(10),Day(@Datetmp))
                if exists(select 1 from master..sysdatabases where name=''+@dbNametmp+'')
                Insert Into @tempDBName Values(@dbNametmp,@BeginTimetmp,@EndTimetmp)
                Set @i=@i+1
            END
        END
    ELSE
        BEGIN
            Set @datediff=datediff(month,@DateBegin,@DateEnd)
            While @i<=@datediff
            BEGIN
                Set @Datetmp=dateadd(month,@i,@DateBegin)
                if(@datediff=0)
                    Begin 
                        Set @BeginTimetmp=@DateBegin 
                        Set @EndTimetmp=@DateEnd 
                    End
                else if @i=0
                    Begin 
                        Set @BeginTimetmp=@Datetmp 
                        Set @EndTimetmp=Convert(Varchar(20),DATEADD(DD,-DAY(DATEADD(M,1,@Datetmp)),DATEADD(M,1,@Datetmp)),23)+' 23:59:59' 
                    End
                else if @i=@datediff
                    Begin 
                        Set @BeginTimetmp=Convert(Varchar(20),DATEADD(DD,-DAY(@Datetmp)+1,@Datetmp),23)+' 00:00:00' 
                        Set @EndTimetmp=@DateEnd 
                    End
                else
                    Begin 
                        Set @BeginTimetmp=Convert(Varchar(20),DATEADD(DD,-DAY(@Datetmp)+1,@Datetmp),23)+' 00:00:00' 
                        Set @EndTimetmp=Convert(Varchar(20),DATEADD(DD,-DAY(DATEADD(M,1,@Datetmp)),DATEADD(M,1,@Datetmp)),23)+' 23:59:59' 
                    END
                    
                Set @dbNametmp='MonthsData'+Convert(varchar(10),year(@Datetmp))++Convert(varchar(10),Right(100+Month(@Datetmp),2))
                if exists(select 1 from master..sysdatabases where name=''+@dbNametmp+'')
                Insert Into @tempDBName Values(@dbNametmp,@BeginTimetmp,@EndTimetmp)
                Set @i=@i+1
            END
        END
    RETURN
END

USE [MonthsData201601]
GO
/****** Object:  StoredProcedure [dbo].[sp_Ct_ReadShopOrderList]    Script Date: 01/06/2016 17:28:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <> 申明临时表 操作临时表 统计子查询 分页(ROW_NUMBER)
-- Create date: <2015-10-23>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Ct_ReadShopOrderList] 
    @dtBegin datetime,   ----开始时间
    @dtEnd datetime,     ----结束时间
    @search VARCHAR(40), ----订单号 / 商品名称
    @pageIndex int,      ----起始页
    @pageSize  INT,      ----页数,
    @status INT,         --1：待付款 2：待发货 3：待收货 4：待评价 5：已完成 0全部
    @OrderType int       --1：商品订单2：积分商品订单
AS
BEGIN
    SET NOCOUNT ON;
    --申明执行语句sql变量(拼接)
    Declare @SQL nvarchar(max),     @strWhere varchar(15),@strTime varchar(100),@orderBy varchar(100);
    
    Set @SQL=''
    
    --删除临时表
    if Object_id('Tempdb..#temp')  IS NOT NULL   DROP TABLE #temp;
    if Object_id('Tempdb..#ProductOrder')  IS NOT NULL   DROP TABLE #ProductOrder;
    if Object_id('Tempdb..#result')  IS NOT NULL   DROP TABLE #result;

    --组织SQL语句 申明游标 
    Declare my_cursor Cursor For Select dbName,convert(varchar(20),BeginTime,120),convert(varchar(20),EndTime,120) From dbo.GetDataBaseName(@dtBegin,@dtEnd,0)
    open my_cursor
    Declare @DBName Varchar(200), @BeginTime varchar(20),@EndTime varchar(20)
    fetch next from my_cursor into @DBName,@BeginTime,@EndTime
    WHILE(@@fetch_status=0)
    BEGIN
       IF @OrderType=2
        BEGIN 
          if @SQL<>''
            Set @SQL=@SQL + ' Union All '   
            Set @SQL=@SQL+ ' SELECT  a.SendTime,  a.CancelTime,  a.ContactPhone, a.ReceiverAddr, a.ReceiveTime, a.[OrderId],a.[CreateTime],a.[Status],[ReceiverName],[OrderType] ,
                                 (SELECT COUNT(b.OrderId) FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) orderCount,
                                 (SELECT TOP 1 Name FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) name,
                                 (SELECT TOP 1 Integral FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) Integral,
                                 (SELECT SUM(BuyingCount) FROM '+@DBName+'..T_ProductOrderDetails  d WITH(NOLOCK) WHERE d.OrderId=a.OrderId and d.IsDeleted=0) productCount
                                  from '+@DBName+'..[T_ProductOrder]  a  WITH(NOLOCK)' 
             fetch next from my_cursor into @DBName,@BeginTime,@EndTime
           END
       ELSE IF @OrderType=1
        BEGIN 
         if @SQL<>''
           Set @SQL=@SQL + ' Union All '
           Set @SQL=@SQL+ ' SELECT  a.SendTime,  a.CancelTime,  a.ContactPhone, a.ReceiverAddr, a.ReceiveTime, a.[OrderId],a.[CreateTime],a.[Status],[ReceiverName],[OrderType],
                                 (SELECT COUNT(b.OrderId) FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) orderCount,
                                 (SELECT TOP 1 Name FROM '+@DBName+'..T_ProductOrderDetails  b WITH(NOLOCK) WHERE b.OrderId=a.OrderId and b.IsDeleted=0) name,
                                 (SELECT SUM(BuyingCount) FROM '+@DBName+'..T_ProductOrderDetails  d WITH(NOLOCK) WHERE d.OrderId=a.OrderId and d.IsDeleted=0) productCount
                                 from '+@DBName+'..[T_ProductOrder]  a  WITH(NOLOCK)'                    
                                  
        fetch next from my_cursor into @DBName,@BeginTime,@EndTime
        END
    END
    Close my_cursor
    Deallocate my_cursor
    if IsNull(@SQL,'')<>''
        --@status为0 查询全部            
        SET @strWhere=' and Status='+ Convert(varchar(20),@status)  
        SET @strTime='  CONVERT(varchar, CreateTime, 120 )  Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '
                 
        IF @status=0
             select @strWhere='', @orderBy  ='CreateTime';
        ELSE IF @status=1
             select @orderBy  ='CreateTime';
        ELSE IF @status=2
             select @orderBy  ='CreateTime';
        ELSE IF @status=3
             select  @orderBy  ='SendTime', @strTime='  CONVERT(varchar, SendTime, 120 )  Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '    
        ELSE IF @status=4
             select @orderBy  ='CreateTime';
        ELSE IF @status=5
             select @orderBy  ='ReceiveTime', @strTime=' CONVERT(varchar, ReceiveTime, 120 )  Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' ' 
        ELSE IF @status=6
             select @orderBy='CancelTime',  @strTime='  CONVERT(varchar, CancelTime, 120 )  Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' ' 
            
        SET @SQL='SELECT * INTO  #temp FROM('+@SQL+' ) temp  WHERE OrderType='+Convert(varchar(20),@OrderType)+' '+@strWhere 
        

        Set @SQL=@SQL+';SELECT * INTO  #ProductOrder FROM #temp  WHERE '+@strTime
        
        SET @SQL=@SQL+'; SELECT * INTO  #result FROM(
                                         SELECT *,ROW_NUMBER() OVER(Order by a.['+@orderBy+'] desc ) as row FROM #ProductOrder a 
                                         WHERE a.OrderId LIKE ''%'+@search+'%''  OR a.name LIKE ''%'+@search+'%''
                                       )temp ;
              SELECT COUNT(1) as count FROM #result;
              SELECT * FROM #result  WHERE row between  '+ Convert(varchar(20),@pageIndex)+' and '+ Convert(varchar(20),@pageSize)                
  -- PRINT      @SQL                                                     
   EXEC sp_executesql  @SQL
END

USE [WiPOBD]
GO
/****** Object:  StoredProcedure [dbo].[sp_Ct_ReadObdData]    Script Date: 01/06/2016 17:37:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<> 申明临时表 操作临时表 统计子查询 分页(ROW_NUMBER)
-- Create date: <2015-10-23>
-- =============================================
ALTER PROCEDURE [dbo].[sp_Ct_ReadObdData] 
	@dtBegin datetime,      ----开始时间
    @dtEnd datetime,        ----结束时间
    @ServiceID VARCHAR(40), ----服务ID
    @pageIndex int,         ----起始页
    @pageSize  INT          ----页数,
AS
BEGIN
	SET NOCOUNT ON;
    --申明执行语句sql变量(拼接)
	Declare @SQL nvarchar(max);
	
	Set @SQL=''
	
	--删除临时表
	if Object_id('Tempdb..#temp')  IS NOT NULL   DROP TABLE #temp;
	if Object_id('Tempdb..#result')  IS NOT NULL  DROP TABLE #result;


	--组织SQL语句 申明游标 
	Declare my_cursor Cursor For Select dbName,convert(varchar(20),BeginTime,120),convert(varchar(20),EndTime,120) From dbo.GetDataBaseName(@dtBegin,@dtEnd,1)
	open my_cursor
	Declare @DBName Varchar(200), @BeginTime varchar(20),@EndTime varchar(20)
	fetch next from my_cursor into @DBName,@BeginTime,@EndTime
	WHILE(@@fetch_status=0)
	BEGIN
        if @SQL<>''
		   Set @SQL=@SQL + ' Union All '
           Set @SQL=@SQL+ ' SELECT  [ObdSpeed] as Lsd_RcdSpeed,TurnSpeed as Lsd_GPSSpeed,Mileage as Lsd_RcdMileage,CanMileage as Lsd_GPSMileage, ServiceID, [GPSTime] ,[RcvTime] ,[OilTotal],[OilNum] ,[WaterT] ,
                                    [Voltage],[LoadValue],[ThrottlePos] ,[InTakeT] ,[AirFlow],[LangOilRevise]
                                   ,[CylinderAngle],[InTakeKpa] ,[TempOil],[ShortOilRevise],[OxygenPos] ,[GearPos] ,[WheelAngle] ,[ObdStatus]
                                  from '+@DBName+'..T_ObdData  a  WITH(NOLOCK,INDEX=readList) '                                         
        fetch next from my_cursor into @DBName,@BeginTime,@EndTime
	END
	Close my_cursor
	Deallocate my_cursor
	PRINT      @SQL  
	if IsNull(@SQL,'')<>''
        SET @SQL='SELECT * INTO  #temp FROM('+@SQL+' ) temp WHERE ServiceID='+Convert(varchar(20),@ServiceID)+' AND  CONVERT(varchar, RcvTime, 120 )  Between '''+ CONVERT(varchar(100), @dtBegin, 120)  +''' And '''+  CONVERT(varchar(100), @dtEnd, 120)+''' '
        SET @SQL=@SQL+'; SELECT * INTO  #result FROM(
                                                      SELECT *, ROW_NUMBER() OVER(Order by a.RcvTime desc ) as row FROM #temp a 
                                                   )temp ;
              SELECT COUNT(1) FROM #result; 
              SELECT * FROM #result  WHERE row between  '+ Convert(varchar(20),@pageIndex)+' and '+ Convert(varchar(20),@pageSize)+' '                                                                   
   EXEC sp_executesql  @SQL
END

---sql还原备份数据脚本

--1.用此语句得到备份文件的逻辑文件名：
RESTORE FILELISTONLY FROM DISK = N'd:\tempdb\olddb.bak' --备份文件存放路径
--看LogicalName，一般会有两个文件,如：
--olddb      --主逻辑文件名称
--olddb_log  --日志逻辑文件名称
 
--2.用以下语句还原数据库
RESTORE DATABASE new_db   
FROM DISK = 'd:\tempdb\olddb.bak' 
WITH MOVE 'olddb' TO 'd:\tempdb\newdb.mdf', 
MOVE 'olddb_log' TO 'd:\tempdb\newdb_log.ldf' 

---demo
USE master

RESTORE FILELISTONLY FROM DISK = N'D:\bfitgpsnew.bak'

----------
RESTORE DATABASE ITSGPSNew20160220   --新数据库名

FROM DISK = 'D:\bfitgpsnew.bak' 
WITH MOVE 'ITSGPSNew20150831' TO 'D:\database\ITSGPSNew20160220.mdf', 
MOVE 'ITSGPSNew20150831_log' TO 'D:\database\ITSGPSNew20160220.ldf'

sql时间

SELECT convert(varchar(20),GETDATE(),120)--2016-10-26 15:01:17 (控制varchar长度)
SELECT convert(varchar(10),GETDATE(),112)--20161026

　sql in 参数化：

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(",", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

项目使用：

/// <summary>
/// var matappList = EntityServiceClientFactory<matapp_dl>.Instance.Search(" CHARINDEX(','+ltrim(CAST (docno as varchar))+',',','+@docno+',')>0", new { docno = sourceno });
/// </summary>
public static Dictionary<string, string> GetParamNames(List<stOtherOutModel> entity, string locationSql, ref string sql)
{
var sourcenoArr = new string[entity.Count];
for (var i = 0; i < entity.Count; i++)
{
sourcenoArr[i] = entity[i].sourceno;
}

var paramNames = sourcenoArr.Select((s, i) => "@tag" + i.ToString()).ToArray();
var list = new Dictionary<string, string>();
for (var i = 0; i < paramNames.Length; i++)
{
list.Add(paramNames[i], sourcenoArr[i]);
}
sql = string.Format(locationSql, string.Join(",", paramNames));
return list;
}

　　参考：http://qa.helplib.com/21761

　分页：

 ALTER PROCEDURE [dbo].[up_Sys_ProcGetPageData] 
    ( @TableName VARCHAR(max), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID 
    @PrimaryKey NVARCHAR(100), --主键，可以带表头 a.AID 
    @Fields NVARCHAR(max) = '*',--读取字段 
    @Condition NVARCHAR(3000) = '',--Where条件 
    @CurrentPage INT = 1, --开始页码 
    @PageSize INT = 10, --页大小 
    @Sort NVARCHAR(max) = '', --排序字段 
    @PriorityWhere nvarchar(4000), --优先显示记录
    @RecordCount INT = 0 OUT,
    @Hit nvarchar(500)=''        --SQL 提示参数 
    ) 
    AS 
    /*
    declare 
        
        @TableName VARCHAR(1000)=' LkpMaterielH a left join LkpMaterielStyle b on a.MaterCode=b.MaterCode ',
        @PrimaryKey NVARCHAR(100)='a.MaterCode', 
        @Fields NVARCHAR(max) = 'a.*,b.WashWeight as fe',
        @Condition NVARCHAR(3000) = ' a.MaterCode>''02-1-0017''',
        @CurrentPage INT = 2, 
        @PageSize INT = 0,
        @Sort NVARCHAR(200) = 'a.MaterCode', 
        @PriorityWhere nvarchar(4000)=' a.MaterCode=''02-1-0017''', 
        @RecordCount INT = 0 ,
        @Hit nvarchar(500) 
    --*/


    DECLARE @strWhere VARCHAR(2000)=' where 1=1 '
    DECLARE @strsql NVARCHAR(max)
    declare @PriorityRdCount int=0

    set @PriorityWhere=ltrim(rtrim(isnull(@PriorityWhere,'')))
    if @Hit is null set @Hit=''

     SET @Sort=ISNULL(@Sort,'')  
    IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0  SET @strWhere = @strWhere+ ' and (' + @Condition + ') ' 
    
    

    IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0) 
        BEGIN 
        IF(@Sort='') 
        SET @Sort = @PrimaryKey  
        --ELSE 
        --SET @Sort = @Sort+ ' , '+@PrimaryKey 
    END

    if @PageSize<=0 set @CurrentPage=1 --@PageSize<=0时返回所有记录

    
    SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere + @Hit
    EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT 
    

    if @PriorityWhere>'' set @strWhere=@strWhere+' and not exists(select 1 where '+@PriorityWhere+' )'

    IF @CurrentPage = 1 --第一页提高性能 
    BEGIN 
        set @strsql=''
        if @PriorityWhere>'' begin 
            set @PriorityWhere=' where '+@PriorityWhere
            set @strsql = 'SELECT @PriorityRdCount = Count(1) FROM ' + @TableName + @PriorityWhere 

            EXECUTE sp_executesql @strsql ,N'@PriorityRdCount INT output',@PriorityRdCount OUTPUT
            set @strsql = '
                select  '+@Fields + ' 
                from ' + @TableName + ' ' + @PriorityWhere+'
                union all'
        end        

        set @strsql=@strsql+'
            SELECT '+ (case when @PageSize<=0 then '' else 'TOP ' + str(@PageSize-@PriorityRdCount) +'' end) +' '+@Fields+ ' 
            FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Sort +' '+ @Hit
    END 
    ELSE 
    BEGIN 
    /* 执行动态sql*/ 
        --DECLARE @START_ID NVARCHAR(50) 
        --DECLARE @END_ID NVARCHAR(50) 
        --SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1) 
        --SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize) 
        --SET @strsql = ' SELECT * 
        --FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
        --'+@Fields+ ' 
        --FROM '+@TableName + @strWhere +') AS XX 
        --WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC' 

        --Sql Sever 2012

        set @strsql=' 
        select '+@Fields+'
        from '+@TableName+@strWhere+' 
        '+char(13)
        set @strsql=@strsql + 'order by '+@Sort+' offset '+cast( @PageSize*(@CurrentPage-1) as nvarchar(20))+' rows fetch next '+cast(@PageSize as nvarchar(20))+' rows only '+ @Hit

    END 
    print @strsql
    EXEC(@strsql) 
    RETURN

异常：

ALTER PROC xxxx
    @xxx INT,                
    @xxxx  INT            
AS
BEGIN
    BEGIN TRY
        --do someing 
    
    END TRY
    BEGIN CATCH
        DECLARE @errMsg NVARCHAR(4000)
        SET @errMsg ='具体信息(比如存储过程名称): '+ERROR_MESSAGE();
        RAISERROR(@errMsg,16,1)
    END CATCH
END

--循环执行存储过程

declare @dt datetime 
set @dt='2017-01-01' 
DECLARE @id INT = 1; 
while (@dt < = '2019-02-28') 
begin 
  DECLARE @yyyymm INT; 
  SET @yyyymm = convert(varchar(6),@dt,112) 
    
  DECLARE   @return_value INT; 
  EXEC   @return_value = [xxxx
        @FDBId = 1, 
        @YYYYMM = @yyyymm, 
        @currUserid = 'an'     
  SELECT  @id,@return_value 
  set @dt = DATEADD(month,1,@dt) 
  SET @id = @id + 1 
end

sql 行转列

相关阅读:
杂写笔记一
 杂写笔记二
 关于Filter的配置
 jsp基础
 Session
实验七——实验目的：巩固分支结构、循环结构、函数和数组的使用方法
 实验 6-1输入n个整数，将它们存入数组a中。输出最大值和它所对应的下标
 作业三-5应用分支与循环结构解决问题——输入五级成绩
 作业三-3应用分支与循环结构解决问题——统计字符
 作业三-6
原文地址：https://www.cnblogs.com/y112102/p/2763734.html