Updating record using CTE?

ALTER PROCEDURE SP_PriceUpdate
-- Add the parameters for the stored procedure here
@PriceRuleID INT = NULL,
@CategoryID INT = NULL,
@SiteID INT = NULL
AS
SET NOCOUNT ON;
BEGIN
WITH PriceCTE(ProductID, Categ开发者_如何学编程oryID, SalePrice)
AS
(
    SELECT 
        ProductID, CategoryID, SalePrice
    FROM
        CAT_Products
    WHERE
        CategoryID = @CategoryID
)
SELECT ProductID,categoryID,SalePrice FROM PriceCTE

DECLARE 
    @Value DECIMAL(32,2),
    @Type NVARCHAR(5),
    @Inc_Dec NVARCHAR(5)

SELECT @Value = value FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID
SELECT @Type = [type] FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID
SELECT @Inc_Dec = Inc_Dec FROM VB_PriceRule WHERE ID = @PriceRuleID AND SiteId = @SiteID

IF(@Type = '+')
    BEGIN
        IF(@Inc_Dec = '%')
            BEGIN
            --print 'MSG'
                UPDATE CAT_Products SET
                    SalePrice = SalePrice + (@Value/100*SalePrice)
                FROM PriceCTE
                WHERE
                    CategoryID = @CategoryID
            END
        ELSE
            BEGIN
                UPDATE CAT_Products SET
                    SalePrice = SalePrice + @Value                      
                FROM PriceCTE
                WHERE
                    CategoryID = @CategoryID
            END
    END
ELSE
    BEGIN
        IF(@Inc_Dec = '%')
            BEGIN
                UPDATE CAT_Products SET
                    SalePrice = SalePrice - (@Value/100*SalePrice)                      
                FROM PriceCTE
                WHERE
                    CategoryID = @CategoryID
            END
        ELSE
            BEGIN
                UPDATE CAT_Products SET
                    SalePrice = SalePrice - @Value
                FROM PriceCTE
                WHERE
                    CategoryID = @CategoryID
            END
    END
END

Q) Here is my query...I want to update the sales price of CAT_Products. As per the conditions using CTE. But it gives me the results as "Invalid object name PriceCTE"

A CTE is valid within the scope of the single statement it is defined in. Therefore, since PriceCTE is defined within the scope of the SELECT … FROM PriceCTE statement, it cannot be accessed in your UPDATEs.

The following solution combines the CTE definition with UPDATE. It also combines all your updates in one statement.

ALTER PROCEDURE SP_PriceUpdate
-- Add the parameters for the stored procedure here
@PriceRuleID INT = NULL,
@CategoryID INT = NULL,
@SiteID INT = NULL
AS
SET NOCOUNT ON;
BEGIN

DECLARE 
    @Value DECIMAL(32,2),
    @Type NVARCHAR(5),
    @Inc_Dec NVARCHAR(5);

SELECT
    @Value = value,
    @Type = [type],
    @Inc_Dec = Inc_Dec
FROM
    VB_PriceRule
WHERE
    ID = @PriceRuleID
AND SiteId = @SiteID;

WITH PriceCTE(ProductID, CategoryID, SalePrice)
AS
(
    SELECT
        ProductID, CategoryID, SalePrice
    FROM
        CAT_Products
    WHERE
        CategoryID = @CategoryID
)
UPDATE PriceCTE
SET
    SalePrice = SalePrice + @Value *
    (
        CASE @Type WHEN '+' THEN 1 ELSE -1 END *
        CASE @Inc_Dec WHEN '%' THEN SalePrice / 100 ELSE 1 END
    )

END

I didn't change variable names, but, as I said in a comment to your question, two variables, @Inc_Dec and @Type, do look as if they need to trade places. I'm merely judging by their names, in particular, by the @Inc_Dec name: that one is more likely to mean + or -, rather than % or whatever is the opposite value. But that's just an assumption, and you must know your stuff better, naturally.