存储过程入门

/* 使用的数据库名 */
use ewmsdb
/* 判断存储过程是否已存在,若已存在,则将之删除 */
if exists (select name from sysobjects where name='wzy_test1' and type='P')
drop proc wzy_test1
GO
/* 创建一个无参的存储过程 */
create proc wzy_test1
as
declare @txt varchar(100)
set @txt='当前日期为:'
select @txt+cast(year(getdate()) as varchar)+'年'+cast(month(getdate()) as varchar)+'月'+ cast(day(getdate()) as varchar)+ '日' as Now_Date
GO
/*执行过程 */
exec wzy_test1
/* 创建一个有参的存储过程 */
if exists (select name from sysobjects where name = 'wzy_test2' and type='P')
drop proc wzy_test2
go
create proc wzy_test2
@id varchar(20) out,
@fn varchar(20) out,
@ln varchar(20) out,
@sex char(2) out,
@bd smalldatetime
as
begin
insert into w_temp_h(ID,First_Name,Last_Name,Sex,Birth_Date) values (@id,@fn,@ln,@sex,@bd)
end
go
/*执行过程 */
exec wzy_test2 '10008','ewe','yiyy','男','1987-10-10'

/* 游标的使用*/
/* 判断存储过程是否已存在,若已存在,则将之删除 */
if exists (select name from sysobjects where name = 'testproc' and type='P')
drop proc testproc
go
create proc testproc
AS
/* 使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息 */
set nocount on
/* 声明两个临时变量 */
declare @col1c varchar(20),@col2c varchar(20)
/* 创建一个临时表 */
create table #ttemp(
colid int identity(1,1) primary key,
col1 varchar(20),
col2 varchar(20)
)
/* 声明两个游标变量,并将w_temp_h和w_temp_d表中的前两条记录分别插入到游标变量中 */
declare cur1 cursor for select top 2 first_name from w_temp_h
declare cur2 cursor for select top 2 phone from w_temp_d
/* 打开游标 */
open cur1
open cur2
/* 检索游标中一条记录并插入到变量中 */
fetch cur1 into @col1c
fetch cur2 into @col2c
/* 将变量中的值插入到临时表中 */
while @@fetch_status =0
begin
insert into #ttemp(col1,col2) values (@col1c,@col2c)
fetch next from cur1 into @col1c
fetch next from cur2 into @col2c
end
/* 关闭游标 */
close cur1
close cur2
/* 释放资源 */
deallocate cur1
deallocate cur2
/* 显示结束 */
print '显示插入临时表的结束'
select * from #ttemp
print '删除临时表'
drop table #ttemp
go