Create PROCEDURE GuestInfoUpdate
-- Add the parameters for the stored procedure here
(--Guest_ID, Int, PK - auto# not for update,
--Trip_ID, int, PK, auto# - not for update,
@Customer_ID int,  --update if null
@FirstName char(50),
@LastName char(50),
@Phone nchar(10) ,
@UserName char(20) , --not for update
@TripDate_Time	datetime ,
@Info1	char(50),
@Info2	nchar(10) ,
@Info3	char(50),
@Info4	char(50),
)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    Insert Into Guests

    --Insert statements for procedure here
	SELECT 
	--Guest_ID, Int, PK - auto# not for update
	@Customer_ID, --update if null
	@FirstName,
	@LastName,
	@Phone
	
Insert Into TripInfo

    -- Insert statements for procedure here
	SELECT 
--Trip_ID, int, PK, auto# - not for update
@Customer_ID, --update if null
@UserName, -- not for update
@TripDate_Time,
@Info1,
@Info2,
@Info3,
@Info4,
max(Guest_ID) from Guests --not for update
 
end

@info1 varchar(10)= NULL,
@Info2 int=NULL
...

then in update do like

UPDATE tab;e
SET Info1=COALESCE(@Info1,Info1),
Info2=COALESCE(@Info2,Info2)
...
WHERE @Info1 IS NOT NULL
OR @Info2 IS NOT NULL
OR...

quote:

---

Originally posted by visakh16

yup. make all the parameters to update as optional like

@info1 varchar(10)= NULL,
@Info2 int=NULL
...

then in update do like

UPDATE tab;e
SET Info1=COALESCE(@Info1,Info1),
Info2=COALESCE(@Info2,Info2)
...
WHERE @Info1 IS NOT NULL
OR @Info2 IS NOT NULL
OR...

then pass values only for those fields you need to change.
the COALESCE will make sure only fields passed are updated and others retain their original value

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

---

quote:

---

Originally posted by motilok

quote:

---

Originally posted by visakh16

yup. make all the parameters to update as optional like

@info1 varchar(10)= NULL,
@Info2 int=NULL
...

then in update do like

UPDATE tab;e
SET Info1=COALESCE(@Info1,Info1),
Info2=COALESCE(@Info2,Info2)
...
WHERE @Info1 IS NOT NULL
OR @Info2 IS NOT NULL
OR...

then pass values only for those fields you need to change.
the COALESCE will make sure only fields passed are updated and others retain their original value

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

---

YOU ARE A GENIOUS!!! You made my life so much easier, and my boss will be happy with the results. Thank you.

---