Stored Procedures to insert into a table

Expand Post »

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

MS SQL Syntax (Toggle Plain Text)
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
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

Similar Threads

Stored Procedures Help please in MySQL
the difference between Stored procedures temporary tables in MS SQL
Code generator that produces SQL Stored Procedures in VB.NET
how can Stored procedures be used in oracle reports for creating reports? in Oracle
How to Execute stored procedures in a javascript or vbscript function in ASP

wchitamb

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

31 posts
since Jul 2005

Permalink

Nov 10th, 2009

Re: Stored Procedures to insert into a table

This should do the trick:

MS SQL Syntax (Toggle Plain Text)
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
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:

text Syntax (Toggle Plain Text)

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

And the final data:

text Syntax (Toggle Plain Text)

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

sknake

Featured Poster

Reputation Points: 1749

Solved Threads: 735

Senior Poster

Offline

3,948 posts
since Feb 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: is there a way to make an image data type as index?

Next Thread in MS SQL Forum Timeline: Connect by prior command

DaniWeb Message

Cancel Changes

User Name
Password