October 12, 2009 at 9:45 pm
Hi everyone,
I had the following stored procedure in my database. But when I execute it from my VB.NET website,it occured error.
CREATE PROC sp_rpt_POByBranches (
@SellerID varchar (50),
@BuyerID varchar (50),
@FromDate datetime,
@ToDate datetime,
@OrderFlagint -- 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:
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:
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
October 13, 2009 at 2:30 am
Are you certain that's the SQL being executed when you get the error?
The SQL you posted doesn't have a syntax error, but the error you posted looks a bit like you've exceeded 8000 characters in one of the variables and the statement has been truncated (PlaceDeli looks like only part of PlaceDelivery).
BTW... I hope you are filtering out invalid characters in the front end application... if not, you are leaving yourself open to a bit of SQL injection.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply