Insert Select Based on IF Condition on a Table Variable

Question

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 = @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 
               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..

Use Merge Statement technet.microsoft.com/en-us/library/bb510625.aspx — Shantanu Gupta
– Shantanu Gupta, Commented Sep 26, 2011 at 8:20

Bogdan Sahlean · Accepted Answer · 2011-09-26 08:19:03Z

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

Collectives™ on Stack Overflow

Insert Select Based on IF Condition on a Table Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related