with a parameter in a sproc, how do i get null to mean all values?

[MBresnan]

I'm trying to put together a sproc to feed a crystal report, but i can't get the report to return any records. I'm assuming because it's reading a null value in the parameter as just that. Here's my Sproc:

create PROC dbo.ACCR_RECEIPTS
@vendorname varchar(50),
@PONumber varchar(50)
AS
Select c.EDFPONUMBER, c.[idfRCVDetailKey],b.vendname,a.idftransactiontype,
c.idfqtyinvoiced, c.idfqtyshipped, c.edfprice,
(c.idfqtyinvoiced * c.edfprice) as [Extended Inv Amt], (c.idfqtyshipped * c.edfprice) as [Extended Rcv Amt],
a.idftransactiontype, c.edfPOline, c.edfponumber

from [CA].[dbo].[RCVHeader] A
left outer join CA.dbo.PM00200 B
on a.edfvendor = b.vendorid
inner join [CA].[dbo].[RCVDetail] C
on a.idfrcvheaderkey = c.idfrcvheaderkey
where a.idftransactiontype between 1 and 2 and b.vendname= isnull(@vendorname, b.vendname) and c.edfponumber=isnull(@PONumber,c.edfponumber)

I want people to be able to enter a specific PO number OR Vendor name, but if they leave it blank they get all values. I've seen a few ways to handle this, and none of them seem to work. any thoughts?

 

[briangriffin]

Often I think it's easier to break it down into components:

if @vendorname is null and @PONumber is null -- this has to come first
BEGIN
select everything
END

else

if @vendorname is null -- @PONumber is not null
BEGIN
select everything where PONumber field = @PONumber
END

else -- @Vendorname is not null

BEGIN
select everything where Vendornamefield = @Vendorname
END

 

[pmax9999]

This question would be better posted in a SQL Server forum, but I think the problem here is your use of isnull.

I don't have access to SQL Server at the moment to test this, and I don't recall having needed to allow for blank parametters in a stored proc before so can't guarantee this would work but I would amend your where clause as follows:

where 	a.idftransactiontype between 1 and 2 and 
	(
		@vendorname is null or
		(
			@vendorname is not null and
			b.vendname= @vendorname
		)
	) 
	and
	(
		@PONumber is null or
		(
			@PONumber is not null and
			c.edfponumber=@PONumber		
		)
	)

This will use the parameters if they exist or ignore them if ommitted.

Hope it helps.

Cheers
Pete

 

[MBresnan]

ARGH, that isn't working either. is there something in the Record Selection formula i need to change? i got this from a blog post

(not hasvalue({?@vendorname}) or {ACCR_RECEIPTS;1.vendname}={?@vendorname}) AND (not hasvalue({?@PONumber}) or {ACCR_RECEIPTS;1.EDFPONUMBER}={?@vendorname})

Or should i use the above code for that?

 

[MBresnan]

Also, if i run the sproc in SQL Mgmt Studio, it works like a charm with PMAX's code. So i'm assuming there is something i'm missing within Crystal Reports. i tried to enter his code into the record selection formula, and it's giving me a "rest of the statement does not appear to be part of the formula" error.

 

[pmax9999]

OK, testing the stored proc in SQL Server itself was going to be my next suggestion, so we know it is returning the correct data.

You should remove any reference to the parameter from Record Selection. If that still doesn't work, it is likely to be the way that CR passes the 'empty' parameter. If it still doesn't work, try amending the stored proc code to check for empty strings rather than nulls. The Where clause would look something like this:

where 	a.idftransactiontype between 1 and 2 and 
	(
		@vendorname = '' or
		(
			@vendorname <> '' and
			b.vendname= @vendorname
		)
	) 
	and
	(
		@PONumber = '' or
		(
			@PONumber <> '' and
			c.edfponumber=@PONumber		
		)
	)

Cheers
Pete

 

[MBresnan]

Sorry if i'm being dense, but what should the record selection code look like? i read somewhere that when you make the parameter optional, you need to amend the selection formula as well. So i did, and made both parameters optional and dynamic (it wont let me change static params to optional). Now when i refresh, i get a prompt for both parameters, as if it were static (i check pass null value), click ok. Then another window with two dynamic parameter fields pops up. only one has the pass null check box though. I check it and click ok. Nothing returns in the report, but i'm getting the correct number of records. am i doubling up on the parameters or something somewhere?

 

[pmax9999]

When using a stored procedure, its parameters will automtaically be passed to the report and do not need to be created manually. Also, when using an SP you should not be including any record selection as it should all be included in the SP code.

Remove the the manually created parameters - does this give you the desired result?


Pete