Stored Procedure minefield... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Stored Procedure minefield...

Author

Topic

clownshoes2009
Starting Member

2 Posts

Posted - 2009-04-30 : 20:35:53

Hi everyone

I'm trying to write a stored procedure to add values to my tables in my database.

Now there are two tables - "Entries" and "EntriesDetail"

In Entries I want to store a users's details.

On the website the user is than able to answer some questions. There are only 4 questions and a tiebreaker. The user can however, choose to answer the questions and tiebreaker more than once.

These values are stored in "EntriesDetail"

I have a made a relationship between the two tables.

"Entries" - So what I need to do is have the insert run once for the user's details and if it sees that there is no Primary Key - it adds the details. If it finds the same primary key then it doesn't insert.

"EntriesDetail" - Since the user can answer the questions more than once I need all their answers recorded and associated with the same Primary Key as that in Entries.

Since the event to trigger the sprocs will in all likelihood be used more than once - it is imperative the Entries table only has new users and the EntriesDetail store all the answers to questions but are linked to the user's details with a key.

I'd really appreciate some help on this as I'm new to anything to do with SQL and particularly Stored Procedures! Below are my two procedures as they currently stand.

ALTER PROCEDURE AddCustomer
(
@OrderID char(36),
@FirstName varchar(50),
@LastName varchar(50),
@PhoneNumber varchar(50),
@Email varchar(50),
@Address varchar(500),
@PostCode varchar(50),
@EntryNumber int
)

AS

IF (NOT EXISTS(SELECT OrderID FROM Entries WHERE OrderID = @OrderID))

INSERT INTO Entries(OrderID, FirstName, LastName, PhoneNumber, Email, Address, PostCode, EntryNumber, DateCreated)

VALUES (@OrderID, @FirstName, @LastName, @PhoneNumber, @Email, @Address, @PostCode, @EntryNumber, GETDATE())

ELSE

UPDATE Entries

SET

FirstName = @FirstName,
LastName = @LastName,
PhoneNumber = @PhoneNumber,
Email = @Email,
Address = @Address,
PostCode = @PostCode,
EntryNumber = @EntryNumber,
DateCreated = GETDATE()

where OrderID = @OrderID

ALTER PROCEDURE AddEntry

@OrderID char(36),
@Question1 varchar(1),
@Question2 varchar(1),
@Question3 varchar(1),
@Question4 varchar(1),
@TieBreaker varchar(500)

AS

INSERT INTO EntriesDetail(OrderID, Question1, Question2, Question3, Question4, TieBreaker)

VALUES (@OrderID, @Question1, @Question2, @Question3, @Question4, @TieBreaker)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources