存储过程

语句格式
---------------------------

 if exists (select * from sysobjects where id = OBJECT_ID('[t]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [t]
 CREATE TABLE [t] (  [id] [int] NOT NULL , [T1] [nvarchar] (50) NULL , [T2] [nvarchar] (50) NULL )

 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 8 , 'A' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 9 , 'D' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 10 , 'F' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 11 , 'D' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 12 , 'F' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 13 , 'F' , '003' )

----
--不带参数
CREATE PROCEDURE Temp_T
as
select * from T

exec Temp_T

---------------------------------------------------------------------------------------------
--带参数
CREATE PROCEDURE Temp_T(@Temp1 as varchar(10),@Temp2 as varchar(10))
as
select * from T where T1=@Temp1 and T2=@Temp2

exec Temp_T 'A','001'

drop PROCEDURE Temp_T

--参数默认值
CREATE PROCEDURE Temp_T(@Temp1 as varchar(10)='A',@Temp2 as varchar(10)='001')  --可以是任意多个
as
select * from T where T1=@Temp1 and T2=@Temp2

exec Temp_T
exec Temp_T 'A','002'

drop PROCEDURE Temp_T

--带返回值的
1.OUTPUT
CREATE PROCEDURE Temp_T(@Temp1 as varchar(10)='F',@Temp2 as varchar(10) OUTPUT)
as
select @Temp2=T2 from T where T1=@Temp1

DECLARE @SC as varchar(10)
exec Temp_T 'F',@SC OUTPUT
PRINT @SC

drop PROCEDURE Temp_T

2.RETURN
2.1 
CREATE PROC IsItOK(@OK VARCHAR(10))
AS
IF @OK='OK'
   RETURN 0
ELSE
   RETURN -100

DECLARE @SC INT
EXEC @SC=IsItOK 'OK'
PRINT @SC
EXEC @SC=IsItOK 'NotOK'
PRINT @SC

2.2
CREATE PROCEDURE Temp_T(@Temp1 as varchar(10)='F',@Temp2 as varchar(10) OUTPUT)
as
select @Temp2=T2 from T where T1=@Temp1
if Exists(SELECT * FROM T where T1=@Temp1)
   RETURN 0

DECLARE @SC as varchar(10), @Temp as int
exec @Temp=Temp_T 'F',@SC OUTPUT
PRINT @SC
PRINT @Temp

注意:RETURN只能返回整型(int) 0-表示成功,-99到-1之间的值是保留值(系统),
-100或者更小的值来返回失败的状态(自定义)

---------------------------------------------------------------------------------------------
--将事务写入存储过程中
Eg:
zjcxc(邹建)老大的解决办法:如下
CREATE PROCEDURE [Insert_BillId]
(@货名            [varchar](10),
 @发货单位[varchar](40),
 @编号[varchar](13)output,
 @返回内容[varchar](40) output)

AS
begin tran
select 编号=right(100000001+isnull(max(编号),0),8)
from wzxx with(tablockx)
where  名称  = @货名 AND 发货单位 = @发货单位
   and ([编号] ='' or [编号] is null )
if @@rowcount=0
begin
    set @返回内容 = '没有符合条件的数据!'
rollback tran
return
end

    UPDATE [aaa].[dbo].[wzxx]
    SET  [编号] = @编号
    WHERE 名称  = @货名 AND 发货单位 = @发货单位
          and ([编号] ='' or [编号] is null )
if @@rowcount=0 or @@error<>0
begin
    set @返回内容 = '没有符合条件的数据!'
rollback tran
return
end
commit tran
go

 

---------------------------------------------------------------------------------------------
--加密的存储过程
CREATE PROCEDURE Temp_T
WITH ENCRYPTION   --加密关键字
as
select * from T

---------------------------------------------------------------------------------------------
--查看存储过程中的代码(加密的不行)
sp_helptext Temp_T