Error Message if Duplicate in one Stored Procedure

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

General SQL Server Forums

Database Design and Application Architecture

Error Message if Duplicate in one Stored Procedure

Author

Topic

motilok
Starting Member

24 Posts

Posted - 2010-08-20 : 12:27:21

I'm still working on the same project. Bellow is my "Insert Into" Stored Procedure. At this time my procedure will insert any data, duplicates or not, it will just create a new Guest_ID and Trip_ID. I need to limit it to insert Data only if Customer_ID is not already in the table and does not have Guest_ID assigned and if Customer_ID has Guest_ID assigned I need it to abort Insert Data and show following error message 'Customer with '+CONVERT(varchar(20),@Customer_ID) +'already has a Guest_ID: '+CONVERT(varchar(20),@guest_ID) +'Please correct Customer_ID or use Guest_ID to Insert new Trip Information'
I would have search around for answers, but I need to get this project complete today.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE Guest
-- Add the parameters for the stored procedure here
(--Guest_ID int, PK in Guests, Not Null, auto#
--Trip_ID int, PK in Trips, not null, auto#
@Customer_ID int,
@FirstName char(20),
@LastName char(20),
@Phone char(21),
@UserName char(20),
@TripDate_Time datetime,
@Info1 char(50),
@Info2 nchar(10),
@Info3 int
)

AS
Begin
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Insert Into Guests
SELECT
@Customer_ID,
@FirstName,
@LastName,
@Phone

--Insert statements for procedure here

Insert Into Trips

-- Insert statements for procedure here
SELECT
@UserName,
@TripDate_Time,
@Info1,
@info2,
@Info3,
max(Guest_ID) from Guests

End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 13:17:44

didnt you get solution here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148840

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

motilok
Starting Member

24 Posts

Posted - 2010-08-20 : 14:12:39

that was different but similar, unfortunately I never learned sql in such depth and not sure how that worked in that solution. There I needed query not to update tables if Guest_ID did go with Trip_ID selected by Customer Rep during updating process. Here I need to stop query inserting data into my two tables if Customer_ID is already in Guests table and has a Guest_ID assigned. Does this makes sence? I have not been thinking totaly clear with all those little issues coming up as soon as I get something working.

motilok
Starting Member

24 Posts

Posted - 2010-08-20 : 14:20:00

also.. Customer_ID can be null, don't know if that would be a problem...

motilok
Starting Member

24 Posts

Posted - 2010-08-23 : 08:31:29

I still need help. I'm behind on this project and have another one to get done by tomorrow. Somebody help please....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources