sqlserver怎么创建存储过程

发布网友发布时间：2022-04-21 19:02

共2个回答

懂视网时间：2022-04-29 23:18

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‘;

View Code

SqlServer存储过程基础

标签：

热心网友时间：2022-04-29 20:26

SQL 创建存储过程：
一、基础语法：
create proc | procere pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
as
SQL_statements

二、常见创建存储过程实例
1、创建不带参数存储过程
create proc proc_get_student
as
select * from student;
执行存储过程：
exec proc_get_student;
2、带参存储过程
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
执行存储过程：
exec proc_find_stu 2, 4;
3、带通配符参数存储过程
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
select * from student where name like @name and name like @nextName;
执行存储过程：
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
4、带输出参数存储过程
create proc proc_getStudentRecord(
@id int, --默认输入参数
@name varchar(20) out, --输出参数
@age varchar(20) output--输入输出参数
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
执行存储过程：
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;