Hi,

I've set up a report page to show a view from a SQL Server database using a stored procedure.

Here's my stored procedure:
<pre id=code><font face=courier size=2 id=code>
CREATE PROCEDURE spLinkReport


@LinkID int,
@StartDate varchar(10),
@EndDate varchar(10)


AS


SET NOCOUNT ON


IF @LinkID = Null AND (@StartDate = Null OR @EndDate = Null)


SELECT dbo.tblLinks.*, dbo.tblLinkClicks.DateClicked AS LinkClickedDate
FROM dbo.tblLinks INNER JOIN dbo.tblLinkClicks ON dbo.tblLinks.LinkID = dbo.tblLinkClicks.LinkID


ELSE IF @LinkID &lt;&gt; Null AND (@StartDate = Null OR @EndDate = Null)


SELECT dbo.tblLinks.*, dbo.tblLinkClicks.DateClicked AS LinkClickedDate
FROM dbo.tblLinks INNER JOIN dbo.tblLinkClicks ON dbo.tblLinks.LinkID = dbo.tblLinkClicks.LinkID
WHERE dbo.tblLinks.LinkID = @LinkID


ELSE IF @LinkID = Null AND @StartDate &lt;&gt; Null AND @EndDate &lt;&gt; Null


SELECT dbo.tblLinks.*, dbo.tblLinkClicks.DateClicked AS LinkClickedDate
FROM dbo.tblLinks INNER JOIN dbo.tblLinkClicks ON dbo.tblLinks.LinkID = dbo.tblLinkClicks.LinkID
WHERE (DateClicked &gt;= CONVERT(DATETIME, @StartDate, 103)) AND (DateClicked &lt;= CONVERT(DATETIME, @EndDate, 103))


ELSE


SELECT dbo.tblLinks.*, dbo.tblLinkClicks.DateClicked AS LinkClickedDate
FROM dbo.tblLinks INNER JOIN dbo.tblLinkClicks ON dbo.tblLinks.LinkID = dbo.tblLinkClicks.LinkID
WHERE dbo.tblLinks.LinkID = @LinkID AND (DateClicked &gt;= CONVERT(DATETIME, @StartDate, 103)) AND (DateClicked &lt;= CONVERT(DATETIME, @EndDate, 103))


SET NOCOUNT OFF
GO
</font id=code></pre id=code>
The problem is this, when I check all the posible combinations of LinkID, StartDate and EndDate in Query Analyser evreything works fine and I get the expected output.

However when I try to add a recordset to the page as follows:
<pre id=code><font face=courier size=2 id=code>
{call dbo.spLinkReport(LinkID,'StartDate','EndDate')}
</font id=code></pre id=code>
With the following variables:
<pre id=code><font face=courier size=2 id=code>
LinkID Defaulte Value - Null Runtime Value - Request("selLinkName"
StartDate Defaulte Value - Null Runtime Value - Request("textStartDate"
EndDate Defaulte Value - Null Runtime Value - Request("textEndDate"
</font id=code></pre id=code>
It half works eg.
<pre id=code><font face=courier size=2 id=code>
ID - Date - Date - works
Null - Date - Date - works
ID - Null - Null - fails
Null - Null - Null - fails
</font id=code></pre id=code>

I made the following chages:

Changed the stored procedure to look for zero values against the date:
<pre id=code><font face=courier size=2 id=code>
IF @LinkID = Null AND @StartDate = 0 OR @EndDate = 0 .....etc.
</font id=code></pre id=code>
Then changed the Recordset variables:
<pre id=code><font face=courier size=2 id=code>
LinkID Defaulte Value - Null Runtime Value - Request("selLinkName"
StartDate Defaulte Value - 0 Runtime Value - Request("textStartDate"
EndDate Defaulte Value - 0 Runtime Value - Request("textEndDate"
</font id=code></pre id=code>
Now when I test the other half works eg.
<pre id=code><font face=courier size=2 id=code>
ID - Date - Date - fails
Null - Date - Date - fails
ID - 0 - 0 - works
Null - 0 - 0 - works
</font id=code></pre id=code>
Has anyone come accross this sort of situation before? How did you solve it?

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 10/17/2001 18:23:52

Solved. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Not elegant but it works.

Owen.