Nathan

Nathan

  • NA
  • 24
  • 14.8k

If statement in SQL Server not working

Feb 17 2014 2:35 AM
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



Answers (4)