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
精彩评论