目前工作中几个常用的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 HolidayL…

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

总结

以上是内存溢出为你收集整理的目前工作中几个常用的SQL存储过程全部内容，希望文章能够帮你解决目前工作中几个常用的SQL存储过程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。