存储过程案例

(
char(65+ceiling(rand()*25)),
char(97+ceiling(rand()*25)),
cast(ceiling(rand()*9) as varchar(1)),
cast(ceiling(rand()*1000) as varchar(10)),
GO
create proc proc_selectProc
@procname varchar(30)
as
begin
select
'parameter_name' = name,
'length' = length,
'xprec' = xprec,
'scale' = odbcscale(xtype,xscale),
GO
create proc proc_selectdate
@Startdate datetime,
@Enddate datetime
as
begin
declare @temp datetime
set @temp=@Startdate
create table #tempdate(Startdate datetime primary key(Startdate),Enddate datetime)--创建临时表
'is_output' = isoutparam,
'type' = type_name(xusertype)
from syscolumns where id = object_id(@procname)
end
go
exec proc_selectProc 'proc_selectdate'
end
end
set @temp=DATEADD(dd,1,@temp)
end
select * from #tempdate
end
go
exec proc_selectdate '2014-1-1','2014-3-1'
go
查询存储过程的详细信息1 money,
decimal2 money,
datetime1 datetime,
datetime12 datetime
)
set @i=1
while( @i<11)
begin
insert into #adata values
dateadd(d,round(rand()*365,0),getdate()),
dateadd(d,round(rand()*365,0),getdate())
)
set @i=@i+1
end
select * from #adata
end
go
exec proc_selecttemp
string varchar(100) not null
)
as
begin
declare @i int,@splen int
select @splen=len(@sign),@i=charindex(@sign,@string)
while @i>0
begin
insert into @uf_StrSplit values(left(@string,@i-1))
字符串的差分输出
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uf_StrSplit]') and OBJECTPROPERTY(id, N'IsFunction') = 1)
drop function uf_StrSplit
drop procedure [dbo].proc_selecttemp
go
create proc proc_selecttemp
as
begin
declare @i int
create table #adata
(
id int identity(1,1),
nvarchar1 varchar(10),
创建临时表，获取时间内的信息
if exists (select * from dbo.sysobjects where id = object_id(N'proc_selectdate') and OBJECTPROPERTY(id, N'IsProcedure') =1)
drop procedure proc_selectdate
go
--创建字符串分拆的函数
create function uf_StrSplit(
@string varchar(8000), --要分拆的字符串
@sign varchar(10) --字符串分隔符
)
returns @uf_StrSplit table(
id int identity(1,1) primary key,
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_selectProc ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure proc_selectProc
select @string=substring(@string,@i+@splen,8000),@i=charindex(@sign,@string)
end
if @string<>''
insert into @uf_StrSplit values(@string)
return
end
go
select * from uf_StrSplit('d,f,g,s,a,d,h,r,w,5,3,8,',',')
第四个存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_selecttemp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
while(@temp<=@Enddate)
begin
if(datepart(dw,@temp)=2)
begin
if(DATEADD(dd,6,@temp)<@Enddate)
begin
insert into #tempdate values (@temp,DATEADD(day,6,@temp))