Must Declare The Scalar Variable &"@row_number&".

Hi Everyone

I have the following stored procedure below, it gives the following error
when I am trying to run it:
Must declare the scalar variable "@row_number"

. Other stored procedures I have run fine.


STORED PROCEDURE
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [sp_find_patients]
@AccessCode varchar(500),
@patientHospNo varchar(255),
@patientFirstName varchar(255),
@patientLastName varchar(255),
@irow_number int
As
declare @sql varchar(8000)
declare @row_number int
SET @row_number = @irow_number

set @AccessCode = "'" + replace(@AccessCode,'-',"','") + "'"

SELECT @sql = 'SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY category.category,sub_category.sub_category,
patientFirstName,patientLastName) AS RowNumber, * FROM patient INNER JOIN sub_category ON
patient.sub_categoryID = sub_category.sub_categoryID
INNER JOIN category ON
sub_category.categoryID = category.categoryID) _myResults
WHERE patient.sub_categoryID in (' + @AccessCode + ') '
IF @patientHospNo <> ''
SELECT @sql = @sql + 'AND patientHospNo = '''+@patientHospNo+''' '
IF @patientFirstName <> ''
SELECT @sql = @sql + 'AND patientFirstName = '''+@patientFirstName+''' '
IF @patientLastName <> ''
SELECT @sql = @sql + 'AND patientLastName = '''+@patientLastName+''' '

SELECT @sql = @sql + 'AND RowNumber > @row_number'

exec (@sql)


I am calling this SP by:

Set rsCmd = Server.CreateObject("ADODB.Command")
rsCmd.ActiveConnection = rsConnection
rsCmd.CommandText = "sp_find_patients"
rsCmd.CommandType = 4
set sp_AccessCode = rsCmd.CreateParameter("@AccessCode",200,1,500, AccessCode)
rsCmd.Parameters.Append sp_AccessCode
set sp_patientHospNo = rsCmd.CreateParameter("@patientHospNo",200,1,255, patientHospNo)
rsCmd.Parameters.Append sp_patientHospNo
set sp_patientFirstName = rsCmd.CreateParameter("@patientFirstName",200,1,255, patientFirstName)
rsCmd.Parameters.Append sp_patientFirstName
set sp_patientLastName = rsCmd.CreateParameter("@patientLastName",200,1,255, patientLastName)
rsCmd.Parameters.Append sp_patientLastName
set sp_row_number = rsCmd.CreateParameter("@row_number",3,1,, row_number)
rsCmd.Parameters.Append sp_row_number
Set rsObj = rsCmd.Execute


Thank you very much.