Problem with a stored Proc in Report

[Cooperdam]

In this Stored Proc, all I did was add the UC field, Unit Price. I have to modify a report. But when I use the new Stored Proc, it only displays 2 fields for use. This is quite confusing to me. I have no idea what the problem is.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

alter Proc [dbo].[_USP_QuotesSummary_ByWeek_Internal]
@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, sop10200.unitcost AS UC,
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)

 

[xlbo]

If you can run the query fine in Mgmt Studio / Query Analyser, try hitting the "Refresh Feilds" option in your dataset properties area

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question