|
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
|
|
Error Message if Duplicate in one Stored Procedure