hi i am using a stored procedure to insert update and delete the records are inserting correctly but it returns the Error code -2.
USE [local]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_OCCAS](@ACTION CHAR(1),@O_ID INT OUT,@O_NAME VARCHAR(50),@O_DESC VARCHAR(500),@O_IMAGE VARCHAR(20),@O_SEQ INT,@O_ACTIVE CHAR(1),@O_UID INT,@ERROR_ID INT OUTPUT)
AS BEGIN
SET NOCOUNT ON;
IF(@ACTION='A')
BEGIN
INSERT INTO TBL_OCCASIONS(O_NAME,O_DESC,O_IMAGE,O_SEQ,O_ACTIVE,O_UID)
VALUES(@O_NAME,@O_DESC,@O_IMAGE,@O_SEQ,@O_ACTIVE,@O_UID);
IF(@@ERROR=0)
SET @ERROR_ID=1
ELSE
SET @ERROR_ID=0
END
IF(@ACTION='U')
BEGIN
IF EXISTS(SELECT O_ID FROM TBL_OCCASIONS WHERE O_ID=@O_ID)
BEGIN
UPDATE TBL_OCCASIONS SET O_NAME=@O_NAME,
O_DESC=@O_DESC,
O_IMAGE=@O_IMAGE,
O_SEQ=@O_SEQ,
O_ACTIVE=@O_ACTIVE
WHERE O_ID=@O_ID
IF(@@ERROR=0)
SET @ERROR_ID=1
ELSE
SET @ERROR_ID=0
END
ELSE
SET @ERROR_ID=-2
END
IF(@ACTION='D')
BEGIN
UPDATE TBL_OCCASIONS SET O_ACTIVE='D' WHERE O_ID=@O_ID
IF(@@ERROR=0)
SET @ERROR_ID=1
ELSE
SET @ERROR_ID=0
END
ELSE
SET @ERROR_ID=-2
IF(@ACTION='E')
BEGIN
UPDATE TBL_OCCASIONS SET O_ACTIVE='Y' WHERE O_ID=@O_ID
IF(@@ERROR=0)
SET @ERROR_ID=1
ELSE
SET @ERROR_ID=1
END
ELSE
SET @ERROR_ID=-2
END
1 Reply
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Khan Abrar AhmedPosted Jun 10, 2014, 7:13 AM
USE [local]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_OCCAS]
(
@ACTION CHAR(1) ,
@O_ID INT OUT ,
@O_NAME VARCHAR(50) ,
@O_DESC VARCHAR(500) ,
@O_IMAGE VARCHAR(20) ,
@O_SEQ INT ,
@O_ACTIVE CHAR(1) ,
@O_UID INT ,
@ERROR_ID INT OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;
IF ( @ACTION = 'A' )
BEGIN
INSERT INTO TBL_OCCASIONS
( O_NAME ,
O_DESC ,
O_IMAGE ,
O_SEQ ,
O_ACTIVE ,
O_UID
)
VALUES
( @O_NAME ,
@O_DESC ,
@O_IMAGE ,
@O_SEQ ,
@O_ACTIVE ,
@O_UID
);
IF ( @@ERROR = 0 )
SET @ERROR_ID = 1
ELSE
SET @ERROR_ID = 0
END
IF ( @ACTION = 'U' )
BEGIN
IF EXISTS ( SELECT
O_ID
FROM
TBL_OCCASIONS
WHERE
O_ID = @O_ID )
BEGIN
UPDATE
TBL_OCCASIONS
SET
O_NAME = @O_NAME ,
O_DESC = @O_DESC ,
O_IMAGE = @O_IMAGE ,
O_SEQ = @O_SEQ ,
O_ACTIVE = @O_ACTIVE
WHERE
O_ID = @O_ID
IF ( @@ERROR = 0 )
SET @ERROR_ID = 1
ELSE
SET @ERROR_ID = 0
END
ELSE
SET @ERROR_ID = -2
END
IF ( @ACTION = 'D' )
BEGIN
UPDATE
TBL_OCCASIONS
SET
O_ACTIVE = 'D'
WHERE
O_ID = @O_ID
IF ( @@ERROR = 0 )
SET @ERROR_ID = 1
ELSE
SET @ERROR_ID = 0
END
ELSE
IF ( @ACTION = 'E' )
BEGIN
UPDATE
TBL_OCCASIONS
SET
O_ACTIVE = 'Y'
WHERE
O_ID = @O_ID
IF ( @@ERROR = 0 )
SET @ERROR_ID = 1
ELSE
SET @ERROR_ID = 1
END
ELSE
SET @ERROR_ID = -2
END