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

以下几个存储过程以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)

转自：http://www.cnblogs.com/ztotem/

生活不易，码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠