创建存储过程

一：没有参数的存储过程

CREATE PROCEDURE select_all
AS
BEGIN
SELECT * from T_login1
END
GO

二：带参数的存储过程

CREATE PROCEDURE select_name

@id uniqueidentifier

AS
BEGIN
SELECT * from T_login1 where PSN0001A=@id
END
GO

三：带通配符参数存储过程

alter proc proc_findStudentByName
@name nvarchar(10)=‘F%’
as
select * from T_login1 where PSN0001A like @name
go

exec proc_findStudentByName ‘F%’

四：带默认值的参数的存储过程

create proc sele_name
@name nvarchar(10)=‘hong3’
as
select * from T_login1 where PSN0002A=@name

exec sele_name ‘lk’
exec sele_name

五：带输出参数的存储过程

create proc [dbo].[p_selectName]

@name nvarchar(10),
@num int output
as
select @num=COUNT(PSN0001A) from T_login1 where PSN0002A=@name

declare @num int
exec p_selectName ‘lk’,@num output
print @num

declare @num1 int
exec p_selectName @name=‘lk’,@num=@num1 output
print @num1

六：临时存储过程

create proc #p_selectName2
as
select COUNT(PSN0001A) from T_login1 where PSN0002A=‘lk’

exec #p_selectName2

七：存储过程的嵌套

alter proc #p_selectName2
as
select COUNT(PSN0001A) from T_login1 where PSN0002A=‘lk’
exec sele_name

exec #p_selectName2

八：不缓存的存储过程

if (object_id(‘proc_temp’, ‘P’) is not null)
drop proc proc_temp1–如果存在不为空，释放掉
go
create proc proc_temp1
with recompile
as
select * from T_login1
go

exec proc_temp1;

九：加密存储过程（无法查看存储过程的代码）

if (object_id(‘proc_temp_encryption’, ‘P’) is not
null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
select * from T_login1;
go

exec proc_temp_encryption;
exec sp_helptext ‘proc_temp1’;
exec sp_helptext ‘proc_temp_encryption’;

十：存储过程中使用if…else

CREATE PROCEDURE pro_numToName
@num int ,
@str nvarchar(5) output
AS
if @num=11
begin
set @str=‘wang’
end
else
begin
set @str=‘qita’
end

生活中的不便，用软件解决，学而不用，白学了-----红马車