Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL server stored procedure error

Author  Topic 

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-12 : 23:30:49
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,
	@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??

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 00:44:30
Change this line:
EXECUTE(@SQL + @SQL1)
to 
select (@SQL + @SQL1)
so we can see what you're executing. Post the return here.




Nathan Skerl
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 00:51:01

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



SELECT(@SQL + @SQL1)
GO
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 00:58:12
No, we need you to alter the proc on your server, then execute it (using inputs that you know have caused the error), and show us the result from your proc.

Does this make sense?
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 01:00:14
The line I asked you to change will return the statement back to the calling application instead of executing it (and throwing the error). In this way we can see what is actually getting executed without triggering the error.
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 01:22:35
the following is the select statement:
SELECT SellerItemNo, BuyerStyleNo, ItemDesc, SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03011,SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))


The following will be error if I using "Execute" instead of "Select":
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'PlaceDeli'.


The following is the stored procedure that I edited to get the parameter in.






CREATE             PROC sp_rpt_POByBranches 
	 	


AS


SET NOCOUNT ON

	DECLARE @SellerID 	varchar (50),
	DECLARE @BuyerID 	varchar (50),
	DECLARE @FromDate 	datetime,
	DECLARE @ToDate 	datetime,
	DECLARE @OrderFlag	int -- 0: Top Sales; 1: Top Quantity; 2: Item Desc
Set SellerID='F9AC0427-5520-420F-841A-FD2EB8A97421'
Set BuyerID='12BBC6AC-B1B7-44AB-A777-5D1B38531BDE'
Set FromDate='2009-10-01'
Set TODate='2009-10-13'
Set OrderFlag='1'

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



SELECT(@SQL + @SQL1)
GO
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 01:41:42
You sure you pasted the entire statement in there? Seems like it got cutoff:

SELECT	SellerItemNo, 
		BuyerStyleNo, 
		ItemDesc, 
		SUM(	CASE	WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) 
						ELSE 0 
				END
		) AS TotalUnit_03011,
		SUM(	CASE	WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 01:52:46
Are you executing this in Management Studio? Make sure you have the options set to return more than the default 256 chars.

You can find these options in Tools > Options > Query Results > SQL Server > Results to Grid -OR- Results to Text.

If those are set too low the GUI will truncate the result.
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 01:54:16
@SQL:
SELECT SellerItemNo, BuyerStyleNo, ItemDesc, SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03011,SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0))

@SQL1:
UNION SELECT NULL, NULL, NULL, SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_03011,SUM(CASE WHEN PT.PlaceDelivery = '03011' THEN (ISNULL(PD.CaseCost,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS


I am using sql server 2000,how can I set the option that you stated?
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 02:05:54
I assume you are using Query Analyzer? Look in Query menu > current connection options > Advanced for a similar option.
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 02:33:47
Hi,

Yes,you are correct,there are some statement left out.Hereby attached posted the latest statement that I get:

@SQL

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
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 03:35:34
The syntax looks fine. Do those statements run ok if you just exec them manually one by one (without the UNION) ?

Nathan Skerl
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 03:44:35
Only thing I can think is if the inputs are different when testing from your app. For example, if curBranch returns many rows then you will quickly exceed the 8000 limit of @SQL param, leading to truncation of one of the case statements... this is what is probably leading to the peculiar error "...near 'PlaceDeli' "

It is being truncated at that point.
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 03:55:53
Ok,so is there anyway or any solution to solved this issues?Or will it be possible to separate the statement because I have no idea on it.
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 04:12:29
Does this query complete without error?


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  

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



Nathan Skerl
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 04:23:01
Yes, both of them completed without error. I am agree with you on the statement that if Curbranch returned many rows, the problem will occured. But if I deleted the looping for branch, the results will not be correct anymore.
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 04:28:33
Ok, i can help tomorrow to resolve 8000 char limit if you still have trouble. This post can help you get started:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274



Nathan Skerl
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-13 : 10:07:31
Thanks...I had viewed the post,but have no idea on it...I will appreciate if you could help me..Thanks,Nathan...
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-13 : 17:46:23
Sorry for delay... here is example of how you can gain another 8000 chars for your case statement loop. As is mentioned in the above link, this method is not optimal, but I suspect you did not write this proc and are simply stuck with fixing the issue at hand.

Keep in mind if that cursor is very large you may need to extend this example script to accommodate more chars.


declare @cmd1 varchar(8000),
		@cmd2 varchar(8000)

set @cmd1 = ''
set @cmd2 = ''

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		
	
		-- check the length
		if len(@cmd1 + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_' + @BuyerGLN + ',') > 8000
		begin
			set @cmd2 = @cmd2 + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_' + @BuyerGLN + ','
		end else
		begin
			set @cmd1 = @cmd1 + 'SUM(CASE WHEN PT.PlaceDelivery = ''' + @BuyerGLN + ''' THEN (ISNULL(PD.PackSize,0) * ISNULL(PT.Qty,0)) ELSE 0 END) AS TotalUnit_' + @BuyerGLN + ','			
		end
		
	fetch from curBranch into @BranchName, @BuyerGLN
	end

close curBranch
deallocate curBranch
Go to Top of Page

daniel50096230
Yak Posting Veteran

99 Posts
Posted - 2009-10-22 : 04:38:35
Hi, after I changing this stored procedure, how should I combine it together with others SQL such as @SQL and @SQL1?
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts
Posted - 2009-10-22 : 12:32:51
You will need to combine within the exec statement in the same way youre currently doing it:
EXECUTE(@SQL + @SQL1)




Nathan Skerl
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site