Strange case of one customer not returning data

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

Strange case of one customer not returning data

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2010-06-11 : 13:43:34

This stored procedure is used for a SSRS report. it returned data for all the customers but not one. How can I go about debugging this sp
btw, there *IS* transactions for the customer, called NADC01 (the customer number.) I am wondering is this line is the dealbreaker:
"dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE"

USE [PBT]
GO
/****** Object: StoredProcedure [dbo].[_USP_QuotesSummary_ByWeek] Script Date: 06/11/2010 13:38:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--[_USP_QuotesSummary_ByWeek] '01/04/2009', '01/08/2009', 'ALL', 'ALL' , 'ALL', '1'

ALTER Proc [dbo].[_USP_QuotesSummary_ByWeek]
@StartDate varchar(25), @EndDate varchar(25), @CustName varchar(250), @Department varchar(4000), @Category varchar(4000),
@SalesTypes Varchar(50)
as

DECLARE @SQL VARCHAR(8000)

if @CustName <> 'ALL'
begin
set @CustName = replace(@CustName,',', ''',''')
set @CustName = '''' + @CustName + ''''
end

if @Department <> 'ALL'
begin
set @Department = replace(@Department,',', ''',''')
set @Department = '''' + @Department + ''''
end

if @Category <> 'ALL'
begin
set @Category = replace(@Category,',', ''',''')
set @Category = '''' + @Category + ''''
end

set @SalesTypes = replace(@SalesTypes,',', ''',''')
set @SalesTypes = '''' + @SalesTypes + ''''

SET @SQL = 'SELECT SOP10200.XTNDPRCE AS Price, SOP10200.QUANTITY AS quantity,
DATEADD(WK, DATEDIFF(WK, 6, SOP10100.DOCDATE), 6) AS ID,
IV40600.UserCatLongDescr as CatD,

SOP10200.ITEMNMBR + CHAR(13) + IV00101.ITEMDESC + CHAR(13) + ''Par Level:'' AS ITEMDESC, IV00101.ITMGEDSC
FROM dbo.SOP10200 AS SOP10200 INNER JOIN
dbo.IV00101 AS IV00101 ON SOP10200.ITEMNMBR = IV00101.ITEMNMBR INNER JOIN
dbo.SOP10100 AS SOP10100 ON SOP10200.SOPTYPE = SOP10100.SOPTYPE AND SOP10200.SOPNUMBE = SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR INNER JOIN
dbo.SOP10106 ON SOP10200.SOPTYPE = dbo.SOP10106.SOPTYPE AND SOP10200.SOPNUMBE = dbo.SOP10106.SOPNUMBE
inner join dbo.IV40600 on IV40600.USCATVAL = IV00101.ITMGEDSC
Where SOP10100.DOCDATE between ''' + @StartDate + ''' and ''' + @EndDate + ''''

IF @Category <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND IV40600.Usercatlongdescr in (' + @Category+ ')'
END

IF @Department <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND sop10106.USRDEF05 in (' + @Department + ')'
END

IF @CustName <> 'ALL'
BEGIN
SET @SQL = @SQL + ' AND dbo.rm00101.CUSTNAME in (' + @CustName + ')'
END

IF @SalesTypes <> ''
BEGIN
SET @SQL = @SQL + ' AND SOP10200.SOPTYPE in (' + @SalesTypes + ')'
END

SET @SQL = @SQL + ' order by IV00101.ITMGEDSC, IV00101.ITEMDESC, DATEADD(WK,DATEDIFF(WK,6,SOP10100.DOCDATE),6)'
--print @SQL
EXEC (@SQL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-11 : 14:45:15

Your stored procedure is too hard to read with all of the dynamic SQL. You'll instead need to post the output of @SQL for customers where it works and then also for the customer where it doesn't work.

In addition to this, you'll need to post sample data for all of the tables involved in this query that represents cases where it works and where it doesn't work.

Without this information we can not help as we aren't familiar with your table design or data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-14 : 04:02:50

I dont see any point in using dynamic sql until you pass object names as parameters
Do you pass values to @Category as a csv?

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 09:28:18

What Mrs. Kizer mean to say is, "that what you're doing is completely the wrong way of going about this"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources