CONTEXT :
I WANT TO UPDATE THE FIELD OF TABLE 'A' TO FALSE
AND I WANT TO EXCECUTE ANOTHER STORED PROCEDURE AND BASED ON ITS RETURNED VALUE
I WANT TO CHECK THE IF CONDITION AND EXECUTE ANOTHER STATEMENT
IS IT POSSIBLE IF IT POSSIBLE HOW
MY SAMPLE CODE :
UPDATE CheckInOut SET IsActive=0,ActionStatus='D'
where VocherType=@VocherType
AND InOutMasterID=@InOutMasterID
AND BranchID=@BranchID
BEGIN
DECLARE COUNT1 AS INT
SELECT COUNT1=EXEC Check_ConfHallStatus @RoomID,@VocherType,@BranchID
IF(COUNT1>0)
BEGIN
UPDATE Rooms SET RoomStatus='Occupied' WHERE RoomID=@RoomID
END
ELSE
BEGIN
UPDATE Rooms SET RoomStatus='Vacant' WHERE RoomID=@RoomID
END
END
----------------------------------------
Check_ConfHallStatus :
ALTER PROCEDURE Check_ConfHallStatus
(
@RoomID BIGINT
--,@ActionStatus VARCHAR(5)
,@VocherType VARCHAR(15)
,@BranchID bigint
)
as
SELECT COUNT(*) AS CNT FROM CheckInOut
WHERE IsActive=1
AND RoomID=@RoomID
AND ActionStatus='A'--@ActionStatus
AND VocherType=@VocherType
AND BranchID=@BranchID
------------------------------------------------
Hemant SrivastavaPosted Dec 22, 2014, 10:36 AM
Firstly I think you need to change in your stored proc 'Check_ConfHallStatus' to retirn value
My changes are in the bold face letter
ALTER PROCEDURE Check_ConfHallStatus
(
@RoomID BIGINT
--,@ActionStatus VARCHAR(5)
,@VocherType VARCHAR(15)
,@BranchID bigint
)
as
BEGIN
DECLARE @Count INT ;
SET @Count = ( SELECT COUNT(*) AS CNT FROM CheckInOut
WHERE IsActive=1
AND RoomID=@RoomID
AND ActionStatus='A'--@ActionStatus
AND VocherType=@VocherType
AND BranchID=@BranchID
)
RETURN @Count
END
Hemant SrivastavaPosted Dec 22, 2014, 10:38 AM
Then in your main Stored procedure, you need to make following minor changes:
UPDATE CheckInOut SET IsActive=0,ActionStatus='D'
where VocherType=@VocherType
AND InOutMasterID=@InOutMasterID
AND BranchID=@BranchID
BEGIN
DECLARE @COUNT1 AS INT
EXEC @COUNT1 = Check_ConfHallStatus @RoomID,@VocherType,@BranchID
IF(@COUNT1 > 0)
BEGIN
UPDATE Rooms SET RoomStatus='Occupied' WHERE RoomID=@RoomID
END
ELSE
BEGIN
UPDATE Rooms SET RoomStatus='Vacant' WHERE RoomID=@RoomID
END
END
Hope this will help