Recordset paging with stored procedure in MSSQL

Hi Jules,
Thanks for the advice. Unfortunately, using a recordset from the recordset behavior will not work in the context of the page due to the functionality required. The idea of using a stored procedure is to return a recordset based on a number of variables on the page and so a recordset behavior would not suffice. Of course, this works fine on other pages on the site and have used it successfully.

Does DW treat a stored procedures recordset differently from that of a 'normal' recordset in this instance? I would have thought that if there's an option there to return a recordset, then surely it would be handled similarly? I have attached the code here for you/someone to check out? Maybe I'm missing something? Have just used the 'Move to First Record' behavior to illustrate the point. Have also attached the code for the stored procedure.

The URL that is parsed is:

http://localhost/portal/sr/sr.asp?ShowAll=A

The error that appears is as follows:

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E10)
Procedure 'GetSR' expects parameter '@CompId', which was not supplied.
/sr.asp, line 217

Even when I supply the @CompId value, I still get the same error.

Anyway, thanks again.

Ben.


CREATE PROCEDURE dbo.GetSR (
@CompId int,
@ShowAll Char(3),
@UserId int,
@SRStatus varchar(20),
@SRType int
)

AS

/* Show All */
if @ShowAll = ''
begin
SELECT * FROM dbo.SR_View
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for current user */
else if @ShowAll = 'U'
begin
SELECT * FROM dbo.SR_View
Where Status <> 'Closed'
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for current company */
else if @ShowAll = 'C'
begin
SELECT * FROM dbo.SR_View
WHERE CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all = P */
else if @ShowAll = 'P'
begin
SELECT * FROM dbo.SR_View
WHERE PeopleId = @UserId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show Status */
else if @ShowAll = 'S'
begin
if @CompId = 0
begin
SELECT * FROM dbo.SR_View
where Status = @SRStatus
ORDER BY [Sequence], Priority, SubmitDate Desc
end

else if @CompId > 0
begin
SELECT * FROM dbo.SR_View
where Status = @SRStatus and CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
end

/* Show Type */
else if @ShowAll = 'T'
begin
if @CompId = 0
begin
SELECT * FROM dbo.SR_View
where TypeId = @SRType
ORDER BY [Sequence], Priority, SubmitDate Desc
end

else if @CompId > 0
begin
SELECT * FROM dbo.SR_View
where TypeId = @SRType and CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
end

/* Show all for admin user */
else if @ShowAll = 'A'
begin
SELECT * FROM dbo.SR_View
Where CompId = @CompId
ORDER BY [Sequence], Priority, SubmitDate Desc
end

/* Show all for normal user user */
else if @ShowAll = 'N'
begin
SELECT * FROM dbo.SR_View
Where PeopleId = @UserId
ORDER BY [Sequence], Priority, SubmitDate Desc
end
GO
Error Type: Text