Play like this.......................
ALTER Procedure [dbo].[InsUpdGuest_Info]
@guest_id numeric(18, 0)=Null,
@membership_id numeric(18, 0)=null,
@guest_name nvarchar(100),
@contact1 nvarchar(20)=null,
@contact2 nvarchar(20)=null,
@landline nvarchar(20)=null,
@email nvarchar(50)=null,
@address nvarchar(500)=null,
@date_of_birth datetime=null,
@spouse_dob datetime=null,
@wed_aniversary datetime=null,
@reffered_by numeric(18, 0)=Null,
@IsActive bit = null,
@user_id numeric(18, 0)=null,
@RetVal As nvarchar(20)= Null output
As
Begin
If Not Exists(Select * From tblGuestInfo Where guest_id<>@guest_id And guest_name = @guest_name)
Begin
if Not Exists(Select * From tblGuestInfo Where guest_id=@guest_id)
Begin
Begin Try
begin tran;
DECLARE @InsertedRows TABLE (ID numeric(18,0))
Insert Into tblGuestInfo(membership_id
,guest_name
,contact1
,contact2
,landline
,email
,address
,date_of_birth
,spouse_dob
,wed_aniversary
,reffered_by
,IsActive
,created
,created_by)
/* To get output ID */
OUTPUT inserted.guest_id Into @InsertedRows
/*---------------*/
Values(@membership_id
,@guest_name
,@contact1
,@contact2
,@landline
,@email
,@address
,@date_of_birth
,@spouse_dob
,@wed_aniversary
,@reffered_by
,@IsActive
,getdate()
,@user_id);
Select @RetVal = Convert(nvarchar,ID) From @InsertedRows
commit;
return 1;
End Try
Begin Catch
rollback;
return 0;
End Catch
End
Else
Begin
Begin try
begin tran;
Update tblGuestInfo Set
membership_id = @membership_id,
guest_name = @guest_name,
contact1 = @contact1,
contact2 = @contact2,
landline = @landline,
email = @email,
address = @address,
date_of_birth = @date_of_birth,
spouse_dob = @spouse_dob,
wed_aniversary = @wed_aniversary,
reffered_by = @reffered_by,
IsActive = @IsActive,
updated = getdate(),
updated_by = @user_id
Where guest_id = @guest_id;
Delete From tblGuest_Family_Info Where guest_id = @guest_id;
Delete From tblGuest_Account Where guest_id = @reffered_by And trans_id=@guest_id And trans_type='REF';
Set @RetVal = Convert(nvarchar,@guest_id);
commit;
End try
Begin Catch
rollback;
Set @RetVal = 'False';
End Catch
End
End
Else
Begin
Set @RetVal = 'Already Existing';
End
End