Dear Sir,
I am in try to set a condition in my sql query. I want to execute this query if satisfy two conditions. Please give the condition to be set in the form.cs also to this 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_isAudited bit
)
AS
BEGIN
IF @as_preDeliveryStatus=1 AND @as_isAudited=0
--will this condition work?, if not please correct to the correct syntax
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