Results 1 to 1 of 1
  1. #1
    Join Date
    Apr 2009
    Posts
    9

    Unanswered: SQL Server stored procedure error

    Hi everyone,

    I had the following stored procedure in my database. But when I execute it from my VB.NET website,it occured error.

    Code:
    
    
    
    CREATE             PROC sp_rpt_POByBranches (
    	 	
    	@SellerID 	varchar (50),
    	@BuyerID 	varchar (50),
    	@FromDate 	datetime,
    	@ToDate 	datetime,
    	@OrderFlag	int -- 0: Top Sales; 1: Top Quantity; 2: Item Desc
    )
    
    AS
    
    
    SET NOCOUNT ON
    
    DECLARE @SQL VARCHAR(8000), 
    	@BranchName VARCHAR (20),
    	@BuyerGLN   VARCHAR (13),
    	@SQL1 VARCHAR(8000)
    
    
    SET @SQL = ''
    SET @SQL = @SQL + 'SELECT  SellerItemNo, BuyerStyleNo, ItemDesc,  '
    
    -- LOOP BRANCH
    DECLARE curBranch CURSOR LOCAL FOR
    SELECT Distinct [Name], GLN FROM Tb_Address
    WHERE CompanyID = @BuyerID
    ORDER BY [Name]
    
    
    OPEN curBranch
    FETCH FROM curBranch INTO @BranchName, @BuyerGLN
    
    	WHILE @@fetch_status = 0
    	BEGIN		
    
    		SET @SQL = @SQL + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_' + @BuyerGLN + ','
    		SET @SQL = @SQL + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_' + @BuyerGLN + ','
    
    	FETCH FROM curBranch INTO @BranchName, @BuyerGLN
    	END
    
    CLOSE curBranch
    DEALLOCATE curBranch
    
    
    SET @SQL = @SQL + 'SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) AS TotalUnit_ALL,   '
    SET @SQL = @SQL + 'SUM((ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))) AS NetAmt_ALL  '
    SET @SQL = @SQL + 'FROM Trn_PO_Trailers PT WITH (NOLOCK) '
    SET @SQL = @SQL + 'LEFT JOIN Trn_PO_Details PD WITH (NOLOCK) ON PD.POID = PT.POID AND PD.[LineNo] = PT.[LineNo]  '
    SET @SQL = @SQL + 'LEFT JOIN Trn_PO P WITH (NOLOCK) ON P.POID = PT.POID   '
    SET @SQL = @SQL + 'WHERE CONVERT(VARCHAR,P.PODt,112) >= ''' + CONVERT(VARCHAR, @FromDate ,112) + '''  '
    SET @SQL = @SQL + 'AND CONVERT(VARCHAR,P.PODt,112) <= ''' + CONVERT(VARCHAR, @ToDate ,112) + '''  '
    SET @SQL = @SQL + 'AND P.SellerID = ''' + @SellerID + ''' '
    SET @SQL = @SQL + 'AND P.BuyerID =  ''' + @BuyerID + '''  '
    SET @SQL = @SQL + 'GROUP BY SellerItemNo, BuyerStyleNo, ItemDesc  '
    
    ------------------------------------------------ SUM COLUMNS
    SET @SQL1 = ''
    SET @SQL1 = @SQL1 + 'UNION '
    
    SET @SQL1 = @SQL1 + 'SELECT  NULL, NULL, NULL,  '
    
    -- LOOP BRANCH
    DECLARE curBranch1 CURSOR LOCAL FOR
    
    -- ATTN: THIS SELECT STATEMENT MUST BE SAME AS VB CODE - GetFieldList() 
    SELECT Distinct [Name], GLN FROM Tb_Address
    WHERE CompanyID = @BuyerID
    ORDER BY [Name]
    
    
    OPEN curBranch1
    FETCH FROM curBranch1 INTO @BranchName, @BuyerGLN
    
    	WHILE @@fetch_status = 0
    	BEGIN		
    
    		SET @SQL1 = @SQL1 + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_' + @BuyerGLN + ','
    		SET @SQL1 = @SQL1 + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_' + @BuyerGLN + ','
    
    	FETCH FROM curBranch1 INTO @BranchName, @BuyerGLN
    	END
    
    CLOSE curBranch1
    DEALLOCATE curBranch1
    
    
    SET @SQL1 = @SQL1 + 'SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) AS TotalUnit_ALL,   '
    SET @SQL1 = @SQL1 + 'SUM((ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))) AS NetAmt_ALL  '
    SET @SQL1 = @SQL1 + 'FROM Trn_PO_Trailers PT WITH (NOLOCK)  '
    SET @SQL1 = @SQL1 + 'LEFT JOIN Trn_PO_Details PD WITH (NOLOCK) ON PD.POID = PT.POID AND PD.[LineNo] = PT.[LineNo]  '
    SET @SQL1 = @SQL1 + 'LEFT JOIN Trn_PO P WITH (NOLOCK) ON P.POID = PT.POID   '
    SET @SQL1 = @SQL1 + 'WHERE CONVERT(VARCHAR,P.PODt,112) >= ''' + CONVERT(VARCHAR, @FromDate ,112) + '''  '
    SET @SQL1 = @SQL1 + 'AND CONVERT(VARCHAR,P.PODt,112) <= ''' + CONVERT(VARCHAR, @ToDate ,112) + '''  '
    SET @SQL1 = @SQL1 + 'AND P.SellerID = ''' + @SellerID + ''' '
    SET @SQL1 = @SQL1 + 'AND P.BuyerID =  ''' + @BuyerID + '''  '
    
    
    -------------------------------------------------
    
    --SORTING 
    IF @OrderFlag = 0 -- Top Sales
    BEGIN
    	SET @SQL1 = @SQL1 + 'ORDER BY   SUM((ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))) DESC, ItemDesc, SellerItemNo, BuyerStyleNo  '
    END 
    ELSE IF @OrderFlag = 1 -- Top Qty
    BEGIN
    	SET @SQL1 = @SQL1 + 'ORDER BY   SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) DESC, ItemDesc, SellerItemNo, BuyerStyleNo   '
    END
    ELSE IF @OrderFlag = 2 -- Item Desc
    BEGIN
    	SET @SQL1 = @SQL1 + 'ORDER BY   ItemDesc, SellerItemNo, BuyerStyleNo '
    END
    
    
    
    EXECUTE(@SQL + @SQL1)
    GO
    The following is the error that I get:
    Incorrect syntax near the keyword 'SELECT'.
    Line 1: Incorrect syntax near 'PlaceDeli'.

    I am not able to figure out what cause the error.

    Can anyone help me to check??

    The following is the select statement that I get from the stored procedure:

    @SQL

    Code:
    SELECT  SellerItemNo, BuyerStyleNo, ItemDesc,  SUM(CASE WHEN PT.PlaceDelivery = '03999' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03999,SUM(CASE WHEN PT.PlaceDelivery = '03999' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_03999,SUM(CASE WHEN PT.PlaceDelivery = '03002' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03002,SUM(CASE WHEN PT.PlaceDelivery = '03002' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_03002,SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) AS TotalUnit_ALL,   SUM((ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))) AS NetAmt_ALL  FROM Trn_PO_Trailers PT WITH (NOLOCK) LEFT JOIN Trn_PO_Details PD WITH (NOLOCK) ON PD.POID = PT.POID AND PD.[LineNo] = PT.[LineNo]  LEFT JOIN Trn_PO P WITH (NOLOCK) ON P.POID = PT.POID   WHERE CONVERT(VARCHAR,P.PODt,112) >= '20091001'  AND CONVERT(VARCHAR,P.PODt,112) <= '20091013'  AND P.SellerID = 'F9AC0427-5520-420F-841A-FD2EB8A97421' AND P.BuyerID =  '12BBC6AC-B1B7-44AB-A777-5D1B38531BDE'  GROUP BY SellerItemNo, BuyerStyleNo, ItemDesc





    @SQL1:

    Code:
    UNION SELECT  NULL, NULL, NULL,  SUM(CASE WHEN PT.PlaceDelivery = '03999' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03999,SUM(CASE WHEN PT.PlaceDelivery = '03999' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_03999,SUM(CASE WHEN PT.PlaceDelivery = '03002' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03002,SUM(CASE WHEN PT.PlaceDelivery = '03002' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS NetAmt_03002,SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) AS TotalUnit_ALL,   SUM((ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))) AS NetAmt_ALL  FROM Trn_PO_Trailers PT WITH (NOLOCK)  LEFT JOIN Trn_PO_Details PD WITH (NOLOCK) ON PD.POID = PT.POID AND PD.[LineNo] = PT.[LineNo]  LEFT JOIN Trn_PO P WITH (NOLOCK) ON P.POID = PT.POID   WHERE CONVERT(VARCHAR,P.PODt,112) >= '20091001'  AND CONVERT(VARCHAR,P.PODt,112) <= '20091013'  AND P.SellerID = 'F9AC0427-5520-420F-841A-FD2EB8A97421' AND P.BuyerID =  '12BBC6AC-B1B7-44AB-A777-5D1B38531BDE'  ORDER BY   SUM((ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0))) DESC, ItemDesc, SellerItemNo, BuyerStyleNo
    Last edited by daniel50096230; 10-13-09 at 04:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •