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";
--数据库临时表 触发器操作多行
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 行转列
