CREATE PROCEDURE Sp_cust_SaveCustomer
--ALTER PROCEDURE cust_SaveCustomer
(@id int, 
@ Surname varchar(50),
@ Initials varchar(50),
@ SEX_CODE varchar(50)) 
AS IF @id=-1 
BEGIN INSERT INTO cust_Customer(Surname,Initials,SEX_CODE) 
SELECT @ Surname,@ Initials,@SEX_CODE   
SELECT @@identity 
END 
ELSE
BEGIN UPDATE cust_Customer 
SET 
Surname= @ Surname ,
Initials =@ Initials ,
SEX_CODE =@ SEX_CODE   
WHERE id=@id 
SELECT @id 
END

IF OBJECT_ID('test_Customer', 'U') IS NOT NULL DROP TABLE test_Customer
GO
CREATE TABLE test_Customer
(
  id int identity(1000, 1) PRIMARY KEY,
  Surname varchar(50),
  Initials varchar(50),
  SEX_CODE varchar(50)
)
GO
IF OBJECT_ID('Sp_test_SaveCustomer', 'P') IS NOT NULL DROP PROCEDURE Sp_test_SaveCustomer
GO
CREATE PROCEDURE Sp_test_SaveCustomer
(
  @id int, 
  @Surname varchar(50),
  @Initials varchar(50),
  @SEX_CODE varchar(50)
) 
AS 
BEGIN
 
Declare @RESULT int
SET @RESULT = 0
 
IF (@id=-1)
BEGIN
  INSERT INTO test_Customer(Surname,Initials,SEX_CODE) VALUES (@Surname, @Initials, @SEX_CODE)
  SET @RESULT = Cast(SCOPE_IDENTITY() AS int)
END ELSE
BEGIN 
  UPDATE test_Customer 
  SET 
  Surname= @Surname,
  Initials =@Initials,
  SEX_CODE =@SEX_CODE
  WHERE id = @id
 
  SET @RESULT = @id
END
 
RETURN @RESULT
 
END
 
 
 
GO
 
Declare @identity int
exec @identity = dbo.Sp_test_SaveCustomer -1, 'Scott', 'SAK', 'M'
PRINT Cast(@identity AS varchar)
 
exec @identity = dbo.Sp_test_SaveCustomer 1000, 'Scott2', 'SAK', 'F'
PRINT Cast(@identity AS varchar)
 
SELECT * FROM test_Customer

 
(1 row(s) affected)
1000
 
(1 row(s) affected)
1000
 
(1 row(s) affected)

id          Surname    Initials   SEX_CODE
----------- ---------- ---------- ----------
1000        Scott2     SAK        F