Optional parameters in Stored Procedure?

Question

Optional parameters in Stored Procedure?

Mike Beckner

SSChasing Mays

Points: 604
More actions
February 27, 2007 at 11:24 am

#176189

Hello all. I'm using SSRS 2000.
I have designed a report which will be used with any of four parameters. That is, the user may enter values on ANY (or NONE) of four possible parameters.
The report is being called from a VB.NET front end, and my plan was to build the WHERE clause of the stored procedure dynamically, based on the values entered or selected in the form controls. I thought I would be able to pass that string to SQL from my app, but so far I haven't been able to figure out how to do so.
My stored procedure is as follows:
ALTER   Procedure dbo.SelectProfitMarginReport
     @SQL varchar(250) --this would have been the string passed to the SP by my app
AS
     SET NOCOUNT ON
     SET ARITHABORT OFF
     SET ANSI_WARNINGS OFF
     SELECT blah1, blah2, blah3, blah4 from MyTable
     WHERE EDPNO is not null
The end of the WHERE clause would be appended with the string built by my VB app. But of course, I can't use "WHERE EDPNO is not null AND @SQL"
Can I just pass a string into a set position in a stored procedure? If there's no way to do this the way I had planned, how can I tell SSRS to use any or none of the available parameters, and pass it that information from my WinForm? I've only used stored procedures for my datasets in SSRS.
Thanks for any info.
March 2, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Don Becker Ten Centuries Points: 1185 More actions · Answer 1

lookup how to handle an array in sql. (google)

You need to pass multiple values in to/thru one parameter, so you need to delimit them, pass the delimited string/array into the proc, then break them up using a UDF or another proc.

Richard L. McCutchen-464598 Valued Member Points: 73 More actions · Answer 2

This optional parameter thing is driving me nuts, can someone please help. I have a report where the user can enter as many items in up to 8 search parameters, so Im trying to use optional parameters. Heres how Im doing it and it isnt working

SELECT

[items]

FROM

WHERE

(@parm1 IS NULL OR [column] = @parm1)

(@parm2 IS NULL OR [column] = @parm2)

......

But Im not getting any results back, even when I know there are results to be had. Anyone got any ideas?

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Try breaking it down an see if any particualr line breaks it or if all are failing. Basic concept you have looks right. Are you sure you are passing NULL thou for the parameters versus an empty string. Check yur configuration in RS report as ability to pass null I think has som definitions there.

Richard L. McCutchen-464598 Valued Member Points: 73 More actions · Answer 4

Before the SELECT I have

If @parm1 = '' SET @parm1 = NULL

......

Then in my application I set the values to String.Empty if theres not a value before sending it to the server

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Have you tried the SP from QA to be sure it is the SP first?

Richard L. McCutchen-464598 Valued Member Points: 73 More actions · Answer 6

Yup, even in QA it returns nothing, but if I specify at least 1 valid items for each parameter it will return results, not what I'm looking for though.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Try adding PRINT lines in your SP to output the parameters to verify what you are putting in is behaving correctly. Maybe

PRINT '' + IsNull(@Parm1,'IMNull') + ''

so if param1 is A you should see

'A'

If a empty string is making that far yuo wll see

''

but null should return

'IMNull'

BTW do the parameters happen to be CHARs and not VARCHARs?