CREATE PROCEDURE saveContactRecord @oID INT OUTPUT, @ID INT, @Name NVarChar(100), @Phone NVarChar(50), @Fax NVarChar(50), @Mobile NVarChar(50), @Email NVarChar(100), @AccountName NVarChar(100), @Position NVarChar(100), @Memo NVarChar(2000) ASDeclare @isExisted INT Declare @AccountID INT Declare @Valid INTSelect @isExisted = count(*) From Contact Where ContactID = @ID Select @AccountID=ID From Account Where Name=@AccountName If @AccountID is null begin Return -1 end SET @Valid = 1 ---- valid record SET @oID = 0If @isExisted = 0 BEGIN ----- Insert this new record Insert Into Contact(ContactName, Phone, Mobile, Fax, Email, AccountID, Position, Memo, Valid) Values(@Name, @Phone, @Mobile, @Fax, @Email, @AccountID, @Position, @Memo, @Valid) IF @@ERROR<>0 BEGIN RAISERROR('insert failed: name=%s', 16, 1, @Name) RETURN -1 END select @oID = @@IDENTITY -- 在程序返回空值null, 但是在VS.NET中单步进入存储过程调试时,是赋值成功的, 比如得到值为15END Else BEGIN ----- Update this record Update Contact Set ContactName=@Name, Phone=@Phone, Mobile=@Mobile, Fax=@Fax, Email=@Email, AccountID=@AccountID, Position=@Position, Memo=@Memo, Valid=@Valid Where ContactID=@ID SET @oID = @ID IF @@ERROR<>0 BEGIN RAISERROR('update failed: id=%d, name=%s', 16, 1, @ID, @Name) RETURN -1 END END--Return 0 GO
@oID INT OUTPUT,
@ID INT,
@Name NVarChar(100),
@Phone NVarChar(50),
@Fax NVarChar(50),
@Mobile NVarChar(50),
@Email NVarChar(100),
@AccountName NVarChar(100),
@Position NVarChar(100),
@Memo NVarChar(2000)
ASDeclare @isExisted INT
Declare @AccountID INT
Declare @Valid INTSelect @isExisted = count(*) From Contact Where ContactID = @ID
Select @AccountID=ID From Account Where Name=@AccountName
If @AccountID is null
begin
Return -1
end
SET @Valid = 1 ---- valid record
SET @oID = 0If @isExisted = 0
BEGIN
----- Insert this new record
Insert Into Contact(ContactName, Phone, Mobile, Fax, Email, AccountID, Position, Memo, Valid)
Values(@Name, @Phone, @Mobile, @Fax, @Email, @AccountID, @Position, @Memo, @Valid)
IF @@ERROR<>0
BEGIN
RAISERROR('insert failed: name=%s', 16, 1, @Name)
RETURN -1
END
select @oID = @@IDENTITY -- 在程序返回空值null, 但是在VS.NET中单步进入存储过程调试时,是赋值成功的, 比如得到值为15END
Else
BEGIN
----- Update this record
Update Contact
Set ContactName=@Name, Phone=@Phone, Mobile=@Mobile, Fax=@Fax, Email=@Email, AccountID=@AccountID, Position=@Position, Memo=@Memo, Valid=@Valid
Where ContactID=@ID
SET @oID = @ID
IF @@ERROR<>0
BEGIN
RAISERROR('update failed: id=%d, name=%s', 16, 1, @ID, @Name)
RETURN -1
END
END--Return 0
GO