开发者

Insert Select Based on IF Condition on a Table Variable

开发者 https://www.devze.com 2023-04-07 06:07 出处:网络
I have declared one Table Variable @OpeningTable. I want to insert the some values based on some conditions using Insert Select. I have written one statement which is given below:

I have declared one Table Variable @OpeningTable. I want to insert the some values based on some conditions using Insert Select. I have written one statement which is given below:

 DECLARE @OpeningTable TABLE
  (
    ItemID INT, OpeningBalance DECIMAL(15,3)
  )
INSERT INTO @OpeningTable 
SELECT

    IF EXISTS (SELECT ItemID, OpeningBalance FROM str_stockdaily WHERE stockdate = @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CustomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1))
          SELECT ItemID, OpeningBalance FROM str_stockdaily WHERE stockdate = @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID=-1) AND (ItemID = @ItemID OR @ItemID = -1)
    ELSE
          IF EXISTS (SELECT ItemID FROM str_stockdaily WHERE stockdate = ISNULL((SELECT MAX(stockdate) FROM str_stockdaily WHERE stockdate < @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)), 0)  AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1))
                SELECT ItemID, closingbalance AS OpeningBalance FROM str_stockdaily WHERE stockdate = (SELECT MAX(stockdate) FROM str_stockdaily WHERE stockdate = @FromDate AND (JobOrderid = @JobOrd开发者_StackOverflow中文版erID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)) AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)
          ELSE 
               SELECT ItemID, 0.000 AS OpeningBalance FROM str_item

But it gives the error:

"Incorrect syntax near the keyword 'IF'

I don't know whether I have written a correct query or not. How to solve this problem? Please I need all your suggestions..


DECLARE @OpeningTable TABLE
  (
    ItemID INT, OpeningBalance DECIMAL(15,3)
  )
--INSERT INTO @OpeningTable 
--SELECT

    IF EXISTS (SELECT ItemID, OpeningBalance FROM str_stockdaily WHERE stockdate = @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1))
        INSERT INTO @OpeningTable 
        SELECT ItemID, OpeningBalance 
        FROM str_stockdaily 
        WHERE stockdate = @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID=-1) AND (ItemID = @ItemID OR @ItemID = -1)
    ELSE
        IF EXISTS (SELECT ItemID FROM str_stockdaily WHERE stockdate = ISNULL((SELECT MAX(stockdate) FROM str_stockdaily WHERE stockdate < @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)), 0)  AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1))
            INSERT INTO @OpeningTable 
            SELECT ItemID, closingbalance AS OpeningBalance 
            FROM str_stockdaily 
            WHERE stockdate = (SELECT MAX(stockdate) FROM str_stockdaily WHERE stockdate = @FromDate AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)) AND (JobOrderid = @JobOrderID OR @JobOrderID = -1) AND (CustomerID = @CUstomerID OR @CustomerID = -1) AND (ItemID = @ItemID OR @ItemID = -1)
        ELSE 
            INSERT INTO @OpeningTable 
            SELECT ItemID, 0.000 AS OpeningBalance 
            FROM str_item
0

精彩评论

暂无评论...
验证码 换一张
取 消