Member Avatar for 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
  • 2 Contributors
  • 1 Reply
  • 219 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

Good start — asked for an insert-or-update stored procedure and posted a solid working example. A few changes will make that pattern safer and more maintainable in production: avoid magic values like -1, return the new id reliably, wrap changes in a transaction with error handling, and use explicit column lists and appropriate datatypes.

Recommended improvements to apply to 's approach:

  • Use @id INT = NULL instead of a sentinel (-1). NULL is clearer and avoids accidental collisions.
  • Return the inserted id via an OUTPUT parameter (or the INSERT ... OUTPUT clause) rather than relying on caller-side PRINT/SELECT or the integer RETURN code.
  • Use SET NOCOUNT ON to avoid extra result messages, and wrap DML in BEGIN TRAN / COMMIT with TRY/CATCH to rollback on error.
  • Prefer NVARCHAR if you need Unicode, validate inputs, and always list columns in INSERT statements.
  • Avoid parameter names with spaces (the original had @ Surname which will fail); use clean names like @Surname.

Example pattern (illustrates OUTPUT + TRY/CATCH; adjust types/lengths to your schema):

CREATE PROCEDURE dbo.Sp_Cust_SaveCustomer
  @id INT = NULL,
  @Surname NVARCHAR(50),
  @Initials NVARCHAR(50),
  @SEX_CODE NVARCHAR(10),
  @NewId INT OUTPUT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Out TABLE (NewId INT);

  BEGIN TRY
    BEGIN TRAN;

    IF @id IS NULL
    BEGIN
      INSERT INTO dbo.cust_Customer (Surname, Initials, SEX_CODE)
      OUTPUT inserted.id INTO @Out
      VALUES (@Surname, @Initials, @SEX_CODE);

      SELECT @NewId = NewId FROM @Out;
    END
    ELSE
    BEGIN
      UPDATE dbo.cust_Customer
      SET Surname = @Surname, Initials = @Initials, SEX_CODE = @SEX_CODE
      WHERE id = @id;

      SET @NewId = @id;
    END

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRAN;
    THROW;
  END CATCH
END

Notes: the INSERT ... OUTPUT approach is robust when triggers exist and avoids race conditions tied to identity functions. Test concurrency, consider optimistic concurrency (rowversion) if updates must detect conflicts, and validate inputs before the DML.

Member Avatar for 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
commented: Thanks sknake +5
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.