two parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ozcan - 28 Feb 2007 01:16 GMT

Hi:

I have two parameters in my report. Date and Name. user should be able to
enter data to one of the parameter field or can enter to both fields to
generate the report. I have below query and it is not working ... If i don't
enter both values i get an error message saying that "enter a value for the
other parameter" ... if i enter for both then it works? What should be the
logic for me to get this working?

Below is the query.
Thanks

SELECT TS_ID, TS_NAME AS [Report Name], TS_CREATEDATE
FROM REPORTS
WHERE (TS_CREATEDATE) >= @CreateDate OR TS_NAME LIKE '%' + @ReportName
+ '%'

Reply to this Message

EMartinez - 28 Feb 2007 03:52 GMT

> Hi:
>
[quoted text clipped - 12 lines]
> WHERE (TS_CREATEDATE) >= @CreateDate OR TS_NAME LIKE '%' + @ReportName
> + '%'

You should do a couple of things. Firstly, you will probably want to
have some sort of default value for both parameters (i.e., empty
string (' '), N/A or 1/1/1900). That way the report itself will not
error out. Secondly, you will want to ignore the defaults in the
query, something like:

IF (TS_CREATEDATE != '1/1/1900' AND TS_NAME != 'N/A')
SELECT TS_ID, TS_NAME AS [Report Name], TS_CREATEDATE
FROM REPORTS
WHERE (TS_CREATEDATE) >= @CreateDate AND TS_NAME LIKE '%' +
@ReportName + '%'
ELSE IF (TS_CREATEDATE = '1/1/1900' AND TS_NAME != 'N/A')
SELECT TS_ID, TS_NAME AS [Report Name], TS_CREATEDATE
FROM REPORTS
WHERE TS_NAME LIKE '%' + @ReportName + '%'
ELSE
SELECT TS_ID, TS_NAME AS [Report Name], TS_CREATEDATE
FROM REPORTS
WHERE (TS_CREATEDATE) >= @CreateDate

Hope this helps.

Regards,

Enrique Martinez
Sr. SQL Server Developer

Reply to this Message