SQL存储过程小记

存储过程分为系统存储过程和用户自定义存储过程。这里参照别人的做一下小的练手，写一下过程。

 

用户自定义存储过程

 

1.创建语法：

create proc|procedure proc_name
(<参数>)
<局部声明>
<过程体>;

2.不带参数的存储过程

CREATE PROCEDURE [dbo].[test_proc]
AS
BEGIN
	select * from dbo.test2
END

执行 exec  [dbo].[test_proc]

 

3.带参数的存储过程

这里的参数和应用程序中的意思相同，声明参数，接收传来的值，使用这些参数时必须先加 @

CREATE PROCEDURE variProc
(
@ID1 int,
@PName char(10)
)
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from dbo.ProWage where <a target=_blank href="mailto:ID=@ID1">ID=@ID1</a> and PName=@PName
END
GO

执行这个存储过程时必须要传值过去 ： exec [dbo].[variProc] 2,'张三'

 

4.修改存储过程时把create改为alter就行了，如果需要改动过程体，修改便是了。

ALTER PROCEDURE [dbo].[test_proc]
	-- Add the parameters for the stored procedure here
	--(@id int,
	--@name varchar(255)
	--)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from dbo.test2
	
END

5.带通配符的参数存储过程

create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

 

 

6.带输入输出参数的存储过程

ALTER PROCEDURE [dbo].[InOutProc]
	-- Add the parameters for the stored procedure here
	(
	@ID1 int,
	@PName char(10) output,
	@price int out
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT @PName=PName,@price=Wage from ProWage where ID=@ID1 and PName=@PName
END

调用这个存储过程，有输出值，set赋值，调用有输入输出的参数时后面得加out、output。

select 后面选择的是要显示出来的列的值，但是没有列名称。

declare @ID1 int, 
        @PName char(10),
		@price int;
set @ID1=1;
set @PName='青鸟';
exec InOutProc @ID1,@PName output,@price out;
select @PName,@price;
--print Cast(@PName as varchar(20)) +'#'+@price
print @price

7.重新编译存储过程

with recompile

ALTER PROCEDURE [dbo].[test_proc]
	-- Add the parameters for the stored procedure here
	--(@id int,
	--@name varchar(255)
	--)	
	with recompile
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from dbo.test2
	
END

 

8.加密存储过程

with encryption   加密存储过程

ALTER PROCEDURE [dbo].[test_proc]
	-- Add the parameters for the stored procedure here
	--(@id int,
	--@name varchar(255)
	--)	
	with encryption
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from dbo.test2
	
END

9.带游标参数存储过程

create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

 

10.分页存储过程

 

---存储过程、row_number完成分页
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from product
;    
    select * from (
        select row_number() over(order by pid) as rowId, * from product 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分页存储过程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

11

Ø Raiserror 

Raiserror返回用户定义的错误信息，可以指定严重级别，设置系统变量记录所发生的错误。 

   语法如下： 

Raiserror({msg_id | msg_str | @local_variable}
  {, severity, state}
  [,argument[,…n]]
  [with option[,…n]]
)

   # msg_id:在sysmessages系统表中指定的用户定义错误信息 

   # msg_str:用户定义的信息，信息最大长度在2047个字符。 

   # severity：用户定义与该消息关联的严重级别。当使用msg_id引发使用sp_addmessage创建的用户定义消息时，raiserror上指定严重性将覆盖sp_addmessage中定义的严重性。 

    任何用户可以指定0-18直接的严重级别。只有sysadmin固定服务器角色常用或具有alter trace权限的用户才能指定19-25直接的严重级别。19-25之间的安全级别需要使用with log选项。 

   # state：介于1至127直接的任何整数。State默认值是1。

raiserror('is error', 16, 1);
select * from sys.messages;
--使用sysmessages中定义的消息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);