目前工作中幾個常用的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/

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈