Nathan

Nathan

  • 1.8k
  • 24
  • 12.8k

If condition in SQL Server

Feb 14 2014 4:11 AM
Dear Sir,
 
I need your help to set a if flag to a SQL Server Delete query. Here the query:-
 
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[Delete_New_invPurchase](
@as_ID bigint,
@upduserid nvarchar(20),
@modidate datetime,
@as_preDeliveryStatus bit

)
AS
BEGIN

IF @as_preDeliveryStatus=1


BEGIN
DECLARE
@rowCount INT,
@i int,
@PID bigint,
@batchno nvarchar(20),
@STOCKLTYPE smallint,
@STOCKGID int,
@STOCK float,
@QTY float,
@FREEQTY float,
@STOCKcompcode nvarchar(3),
@STOCKdivcode nvarchar(3),
@STOCKyearcode nvarchar(5)

DECLARE
@tempTable table
(
RowID int not null primary key identity(1,1),
PID bigint,
batchno nvarchar(20),
LTYPE smallint,
GID int,
STOCK float,
QTY float,
FREEQTY float,
compcode nvarchar(3),
divcode nvarchar(3),
yearcode nvarchar(5)
)

INSERT INTO @tempTable(PID, batchno, QTY, FREEQTY, LTYPE, STOCK, GID, compcode, divcode, yearcode)
SELECT
INVPURCHASEITEMS.PID,
INVPURCHASEITEMS.batchno,
INVPURCHASEITEMS.qty,
INVPURCHASEITEMS.FREEQTY,
INVSTOCK.LTYPE,
INVSTOCK.QTY AS STOCK,
INVSTOCK.GID,
INVSTOCK.Compcode,
INVSTOCK.DivCode,
INVSTOCK.Yearcode
FROM INVPURCHASEITEMS
INNER JOIN INVSTOCK ON INVPURCHASEITEMS.PID = INVSTOCK.PID
INNER JOIN INVPURCHASE ON INVPURCHASE.ID = INVPURCHASEITEMS.PURCHASEID
WHERE INVPURCHASEITEMS.PURCHASEID = @as_ID AND INVPURCHASEITEMS.isdeleted = 'False'
AND INVPURCHASE.LTYPE = INVSTOCK.LTYPE AND INVPURCHASE.YEARCODE = INVSTOCK.YEARCODE
AND INVPURCHASE.GID = INVSTOCK.GID AND INVPURCHASEITEMS.batchno = INVSTOCK.batchno

SET @rowCount = @@ROWCOUNT
SET @i = 0

WHILE (@i < @rowCount)
BEGIN
SET @i=@i+1
SELECT
@PID = PID,
@batchno = batchno,
@STOCKLTYPE = LTYPE,
@STOCKGID = GID,
@STOCK = STOCK,
@QTY = QTY,
@FREEQTY = FREEQTY,
@STOCKcompcode = compcode,
@STOCKdivcode = divcode,
@STOCKyearcode = yearcode
FROM @tempTable
WHERE RowID=@i

IF (@STOCK - (@QTY + @FREEQTY)) > 0
BEGIN
UPDATE INVSTOCK SET qty = @STOCK - (@QTY + @FREEQTY)
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END
ELSE
BEGIN
UPDATE INVSTOCK SET qty = 0
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END

END
END

UPDATE INVPURCHASE SET isdeleted = 'True', upduserid = @upduserid, modidate = getdate()
WHERE ID = @as_ID

UPDATE INVPURCHASEITEMS SET isdeleted = 'True'
WHERE PURCHASEID = @as_ID


END
Before executing this query, I want to check( there is a column named isaudited (datatype: bit) whether the isaudited column is false. if it false, the delete query will be executed, if not return false.

Brought to you by:

Answers (3)