Stored Procedures to insert into a table

[wchitamb]

I have some tables that i have created in SQL Server and i want to write a stored Procedure that will insert data on to one of my tables AND also be able to update say if i select record

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

[sknake]

This should do the trick:

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

Results in:

(1 row(s) affected)
1000

(1 row(s) affected)
1000

(1 row(s) affected)

And the final data:

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