目前工作中几个常用的存储过程

以下几个存储过程以HolidayList表为基础

--创建节假日表
CREATE   TABLE   [ dbo ] . [ HolidayList ]  (
     [ HolidayId ]   [ int ]   IDENTITY  ( 1 ,  1 )  NOT   NULL  ,
     [ HolidayDate ]   [ smalldatetime ]   NULL  ,
     [ HolidayName ]   [ varchar ]  ( 16 ) COLLATE Chinese_PRC_CI_AS  NULL  
)  ON   [ PRIMARY ]

--插入节假日数据
insert  HolidayList
select   ' 2009-10-1 ' , ' 国庆节 '   union  
select   ' 2009-10-2 ' , ' 国庆节 '   union  
select   ' 2009-10-3 ' , ' 国庆节 '   union  
select   ' 2009-10-4 ' , ' 国庆节 '   union  
select   ' 2009-10-5 ' , ' 国庆节 '   union  
select   ' 2009-10-6 ' , ' 国庆节 '   union  
select   ' 2009-10-7 ' , ' 国庆节 '   union  
select   ' 2009-10-8 ' , ' 中秋节 '  

判断当天是否有行情：

CREATE   PROC  dbo.IsQuoteDate
@bQuoteDate   bit  output
AS   
/*
description :    判断当天是否有行情
return :    @bQuoteDate(0:无行情/1:有行情)
author :    totem
create date :   2009-09-16
*/
declare   @currDay   varchar ( 10 ), @HolidayName   varchar ( 16 )
set   @currDay   =   convert ( varchar ( 10 ), getdate (), 121 )
set @bQuoteDate = 0
if   datepart (w, getdate ()) <> 7   and   datepart (w, getdate ()) <> 1   -- 不为周六和周日
begin
   select   @HolidayName   =  holidayName  from  holidaylist  where  holidaydate  =   @currDay
   if   @HolidayName   is   null
     set   @bQuoteDate   =   1
   else
     set   @bQuoteDate   =   0
end

调用方式：

declare   @bQuote   bit
exec  IsQuoteDate  @bQuote  output
print   ' result =  '   +   convert ( char ( 1 ), @bQuote )

获取当前日期的上一有行情的日期：

CREATE   PROC  GetLastQuoteDate
@CurrDay   smalldatetime  ,
@LastQuoteDate   smalldatetime  output
AS
/*
description :    获取指定日期的上一有行情的日期
author :    totem
create date :   2009-09-16
*/
declare   @HolidayName   varchar ( 16 )
declare   @bSuccessed   bit

set   @LastQuoteDate   =   dateadd (d, - 1 , @CurrDay )
set   @bSuccessed   =   0

while   @bSuccessed   =   0
begin
     if   datepart (w, @LastQuoteDate ) = 7   or   datepart (w, @LastQuoteDate ) = 1
     begin
         set   @LastQuoteDate   =   dateadd (d, - 1 , @LastQuoteDate )
         continue
     end
     set   @HolidayName   =   null
     select   @HolidayName   =  holidayName  from  holidaylist  where  holidaydate  =   convert ( varchar ( 10 ), @LastQuoteDate , 121 )
     if   @HolidayName   is   null  
         break
     else
         set   @LastQuoteDate   =   dateadd (d, - 1 , @LastQuoteDate )
end

调用方式：

declare   @currDay   smalldatetime
declare   @QuoteDate   smalldatetime
set   @currDay   =   getdate ()
exec  GetLastQuoteDate  @currDay ,  @QuoteDate  output
print   ' result =  ' +   convert ( varchar ( 10 ), @QuoteDate , 121 )

 

转载于:https://www.cnblogs.com/ztotem/archive/2009/09/16/1568139.html