Gautam Buddha Temple Varanasi,Varanasi - Buddhist Pilgrimage

Saturday, March 27, 2010

Single Stored Procedure for Insert and update record

It good idea to use a single stored proceude instead of write two stored procedure for insert and update.So i am here explainig how to do this.

here we can check if EnquiryId ==0 then insert otherwise update.

Create PROCEDURE [dbo].[uspEnquiry_AddEdit]
(
@EnquiryId bigint
, @EnquiryNo varchar(20) output
, @ClientId bigint
, @ContactPerson varchar(50)
, @Desig varchar(50)
, @Mobile varchar(15)
, @Phone varchar(50)
, @EmailId varchar(100)

)
AS
DECLARE @Message varchar(100)
IF(@EnquiryId=0)
BEGIN
IF EXISTS(SELECT * FROM Enquiry WHERE EnquiryNo=@EnquiryNo)
SET @Message='Record Exists In Database...!'

ELSE
BEGIN
INSERT INTO Enquiry
(
[EnquiryNo]
,[ClientId]
,[ContactPerson]
,[Desig]
,[Mobile]
,[Phone]
,[EmailId]
)
VALUES
(
@EnquiryNo
,@ClientId
,@ContactPerson
,@Desig
,@Mobile
,@Phone
,@EmailId
)
SELECT @EnquiryId = @@IDENTITY;
END
END
ELSE
BEGIN
UPDATE Enquiry
SET [EnquiryNo] = @EnquiryNo
,[ClientId] = @ClientId
,[ContactPerson] = @ContactPerson
,[Desig] = @Desig
,[Mobile] = @Mobile
,[Phone] = @Phone
,[EmailId] = @EmailId
WHERE EnquiryId = @EnquiryId

END
SELECT @EnquiryId


Thanks & Regards
Santosh Singh

No comments:

Post a Comment