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
+ '%'

[quoted text, click to view]

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