SqlServer存储过程

发布网友 发布时间：2022-04-20 00:43

我来回答

共2个回答

懂视网 时间：2022-04-07 22:20

USE Northwind; 2 GO 3 4 IF exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[sp_name]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘)= 1) 5 BEGIN --判断数据库中是否已经存在该存储过程 6 DROP PROCEDURE sp_name; 7 END 8 GO 9 CREATE PROCEDURE sp_name 10 AS 11 BEGIN 12 SELECT * FROM Employees 13 END 14 EXEC sp_name; View Code

二.带输入参数

 1 IF exists (select * from dbo.sysobjects where id =object_id(N‘[dbo].[sp_Employees_GetById]‘) and OBJECTPROPERTY(id, N‘IsProcedure‘)= 1)
 2 BEGIN /*判断数据库中是否已经存在该存储过程*/
 3 DROP PROCEDURE sp_Employees_GetById;
 4 END
 5 GO
 6 CREATE PROCEDURE sp_Employees_GetById
 7 (
 8 @LastName varchar(20),
 9 @FirstName varchar(20)
10 )
11 AS
12 BEGIN
13 SELECT TOP 3 * FROM Employees e WHERE e.FirstName = @FirstName AND e.LastName = @LastName;
14 END
15 --执行
16 EXEC sp_Employees_GetById ‘Davolio‘,‘Nancy‘;

SqlServer存储过程基础

标签：

热心网友 时间：2022-04-07 19:28

create
procere
prCreateSubPlan
as
begin
declare
@id
int,
@intCycle
int,
@planName
varchar(100),
@createTime
smalldatetime,
@cycleTime
int
select
@id
=
min(t_cplan_id)
from
t_cplan
while
(@id
is
not
null)
begin
select
@planName=t_plan_name,
@createTime
=
createTime,
@cycleTime
=
cycleTime
from
t_cplan
where
t_cplan_id=@id
select
@intCycle=
0
while
(@intCycle<@cycleTime)
begin
--
表t_plan
列t_plan_id是IDENTITY
列
insert
t_plan
(t_plan_name,
t_cplan_id,
createTime)
values
(@planName,
@id,
dateadd(day,
@intCycle,
@createTime))
select
@intCycle
=
@intCycle
+
1
end
select
@id
=
min(t_cplan_id)
from
t_cplan
where
t_cplan_id>@id
end
end
go