SQLTeam.com | Weblogs | Forums

Help with sql syntax


#1

hii
i have this query

CREATE PROCEDURE [dbo].[SP_GetAvailableStock] (@StockType INT, @CategoryID INT)
AS
BEGIN
BEGIN TRY
DECLARE @StartRange INT
DECLARE @EndRange INT
IF (@StockType = 0)
BEGIN
SET @StartRange = 0
SET @EndRange = 0
END
IF(StockType = 1)
BEGIN
SET @StartRange=1
SET @EndRange=10
END
IF(@StockType = 2)
BEGIN
SET @StartRange=11
SET @EndRange=10000
END
IF (@CategoryID = 0)
BEGIN
SELECT * FROM ( SELECT
P.Name,
P.Price,
P.ImageUrl,
C.CategoryName,
P.ProductQuantity,
Isnull(Sum(CP.TotalProduct),0) As ProductSold,
( P.ProductQuantity - Isnull(Sum(CP.TotalProduct), 0) ) AS AvailableStock
FROM Products P
INNER JOIN Category C
ON C.CategoryID = P.CategoryID
left join CustomerProducts CP
ON CP.ProductID = P.ProductID
GROUP BY CP.ProductID,
P.Name,
P.Price,
P.ImageUrl,
C.CategoryName,
P.ProductQuantity) StockTable
WHERE AvailableStock BETWEEN @StartRange AND @EndRange
END
ELSE
BEGIN
SELECT *
FROM (SELECT P.Name,
P.Price,
P.ImageUrl,
C.CategoryName,
P.ProductQuantity,
Isnull(Sum(CP.TotalProduct), 0) AS ProductDold
( P.ProductQuantity - Isnull (Sum (CP.TotalProduct) , 0 )) AS AvailableStock
FROM Products P
INNER JOIN Category C
ON C.CategoryID = P.CategoryID
LEFT JOIN CustomerProducts CP
ON CP.ProductID = P.ProductID
WHERE C.CategoryID = P.CategoryID
GROUP BY CP.ProductID,
P.Name,
P.Price,
P.ImageUrl,
C.CategoryName,
P.ProductQuantity) StockTable
WHERE AvailableStock BETWEEN @StartRange AND @EndRange
END
END TRY
BEGIN CATCH
PRINT ('Error occured')
END CATCH
END

I get this error

Msg 102, Level 15, State 1, Procedure SP_GetAvailableStock, Line 54
Incorrect syntax near '('.

wath i need to do ?


#2

missing comma after ProductDold


#3

wath you meen can you show me ?


#4
CREATE PROCEDURE [dbo].[SP_GetAvailableStock] (
	@StockType INT,
	@CategoryID INT
	)
AS
BEGIN
	BEGIN TRY
		DECLARE @StartRange INT
		DECLARE @EndRange INT

		IF (@StockType = 0)
		BEGIN
			SET @StartRange = 0
			SET @EndRange = 0
		END

		IF (StockType = 1)
		BEGIN
			SET @StartRange = 1
			SET @EndRange = 10
		END

		IF (@StockType = 2)
		BEGIN
			SET @StartRange = 11
			SET @EndRange = 10000
		END

		IF (@CategoryID = 0)
		BEGIN
			SELECT *
			FROM (
				SELECT P.NAME,
					P.Price,
					P.ImageUrl,
					C.CategoryName,
					P.ProductQuantity,
					Isnull(Sum(CP.TotalProduct), 0) AS ProductSold,
					(P.ProductQuantity - Isnull(Sum(CP.TotalProduct), 0)) AS AvailableStock
				FROM Products P
				INNER JOIN Category C
					ON C.CategoryID = P.CategoryID
				LEFT JOIN CustomerProducts CP
					ON CP.ProductID = P.ProductID
				GROUP BY CP.ProductID,
					P.NAME,
					P.Price,
					P.ImageUrl,
					C.CategoryName,
					P.ProductQuantity
				) StockTable
			WHERE AvailableStock BETWEEN @StartRange
					AND @EndRange
		END
		ELSE
		BEGIN
			SELECT *
			FROM (
				SELECT P.NAME,
					P.Price,
					P.ImageUrl,
					C.CategoryName,
					P.ProductQuantity,
					Isnull(Sum(CP.TotalProduct), 0) AS ProductDold, -- HERE
					(P.ProductQuantity - Isnull(Sum(CP.TotalProduct), 0)) AS AvailableStock
				FROM Products P
				INNER JOIN Category C
					ON C.CategoryID = P.CategoryID
				LEFT JOIN CustomerProducts CP
					ON CP.ProductID = P.ProductID
				WHERE C.CategoryID = P.CategoryID
				GROUP BY CP.ProductID,
					P.NAME,
					P.Price,
					P.ImageUrl,
					C.CategoryName,
					P.ProductQuantity
				) StockTable
			WHERE AvailableStock BETWEEN @StartRange
					AND @EndRange
		END
	END TRY

	BEGIN CATCH
		PRINT ('Error occured')
	END CATCH
END