--CREATE TABLE TEST (ID INT , NAME VARCHAR(100))
SELECT * FROM TEST
USP_TEST 1, 'REDDY', NULL
USP_TEST 2, 'RAM', NULL
USP_TEST 3, 'SFGGH', NULL
CREATE PROCEDURE USP_TEST
(
@ID INT ,
@NAME VARCHAR(100),
@STATUS INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
IF NOT EXISTS (SELECT 1 FROM TEST WHERE ID = @ID)
BEGIN
INSERT INTO TEST VALUES (@ID, @NAME)
SET @STATUS = 1
PRINT 'Inserted Sucessfully'
END
ELSE
BEGIN
UPDATE TEST SET NAME = @NAME WHERE ID = @ID
SET @STATUS = 2
PRINT 'Updated Sucessfully'
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
SET @STATUS = 0
PRINT 'Invalid Data'
ROLLBACK
END CATCH
END
No comments:
Post a Comment