| 01 | --挂起业务存储过程 |
| 02 | create procedure [dbo].[p_suspend_business] |
| 03 | @Bfixednum varchar(40) |
| 04 | as |
| 05 | begin transaction |
| 06 |
| 07 | declare @SNumber int |
| 08 | declare @messageNumber int |
| 09 | select @SNumber=SNumber from sendnumber where Bfixednum=@Bfixednum |
| 10 | select @messageNumber = count(@Bfixednum) from [message] where Bfixednum = @Bfixednum and mDate between (select dateadd(dd,-day(getdate())+1,getdate())) and (select dateadd(dd,-day(getdate()),dateadd(m,1,getdate()))) |
| 11 |
| 12 |
| 13 | --参数检查 |
| 14 | if (@SNumber is null or @messageNumber = '') |
| 15 | begin |
| 16 | raiserror('错误!缺少参数 ,请检查!',16,1) |
| 17 | rollback |
| 18 | return |
| 19 | end |
| 20 |
| 21 | if (@messageNumber >= @SNumber) |
| 22 | begin |
| 23 | update dbo.Business set BState=0 where Bfixednum=@Bfixednum |
| 24 | commit transaction |
| 25 | end |
| 26 |
| 27 | GO |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 | 利用系统存储过程建立数据库链接 |
| 34 | EXEC sp_addlinkedserver |
| 35 | 'lkTest', |
| 36 | '', |
| 37 | 'MSDASQL', |
| 38 | NULL, |
| 39 | NULL, |
| 40 | 'DRIVER={SQL Server};SERVER=地址;UID=sa;PWD=密码;' |
| 41 | go |
| 42 |
| 1 | --测式数据库语句运行的时间 |
| 2 | declare @starttime as datetime; |
| 3 | declare @endtime as datetime; |
| 4 | set @starttime = getdate(); |
| 5 | --运行的语句 |
| 6 | select * from dbo.total where companyinfo like '%中南一路%' |
| 7 | set @endtime = getdate();select @starttime as 开始时间,@endtime as 结束时间,datediff(MS,@starttime,@endtime)as 运行时间 |
| 8 |
| 9 |