存储过程(Procudure)

学习存储过程，并记录下来。

1.简单的存储过程

CREATE  PROCEDURE selGetdata
AS
SELECT DISTINCT ISNULL(client_code,1) AS client_code
 FROM mapping_commission ORDER BY client_code ASC

 

---

执行

EXEC selGetdata

 

2.带参数的存储过程

CREATE PROCEDURE insertClinetCodeData
@clientcode NVARCHAR(20),
@aecode VARCHAR(20)
AS
declare @result int //声明返回值
IF (@clientcode NOT IN (SELECT  client_code FROM mapping_commission))
BEGIN
INSERT INTO mapping_commission(client_code,aecode)VALUES(@clientcode,@aecode)
set @result=@@ERROR
select @result as result
END;

 

执行

exec insertClinetCodeData 'C100103','C100'

 

3.带事务管理的存储过程

CREATE PROCEDURE translationClientData
@clientcode NVARCHAR(20),
@aecode VARCHAR(20)
AS
BEGIN
DECLARE @count INT
 BEGIN  TRANSACTION
 SELECT @count=COUNT(aecode) FROM commission_ae_order WHERE aecode=@aecode
IF (@count=0)
    BEGIN  
    INSERT INTO commission_ae_order(POSITION,aecode)VALUES((SELECT ISNULL(MAX(POSITION)+1,1)
    FROM commission_ae_order),@aecode)  
    END
ELSE
    BEGIN
    INSERT INTO mapping_commission(client_code,aecode)VALUES(@clientcode,@aecode)  
    END
IF(@@ERROR<>0)
    BEGIN
    ROLLBACK TRANSACTION
    RETURN 0
    END
ELSE
    BEGIN
    COMMIT TRANSACTION
    RETURN 1
    END
END

执行
EXEC insertClinetCodeData 'C55555','C111'