Must declare the scalar variable "@row_number".

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable "@row_number".

Author

Topic

robson
Starting Member

22 Posts

Posted - 2008-04-28 : 04:38:00

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 04:51:07

You cant use the variable @row_number directly inside dynamic sql string. At run time EXEC cant find the declaration of varaible. I think you need to use sp_executesql and pass the value of variable as one of parameters to string. look into books on line for syntax.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 05:07:23

I think this hsould work

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 nvarchar(4000),@Paramdefinition nvarchar(1000)


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

SELECT @sql = N'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 myResults.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'
SET @Paramdefinition =N'@row_number int'

exec sp_executesql @sql,@Paramdefinition,@row_number=@irow_number

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2008-04-28 : 05:21:51

just change this...

SELECT @sql = @sql + 'AND RowNumber > '+ convert(varchar(5),@row_number)

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 05:45:47

You can't use the alias name ROWNUMBER within same scope to check if the value is greater than something else.
Make the query a servied table.

ALTER PROCEDURE	sp_find_patients
(
	@AccessCode varchar(500),
	@patientHospNo varchar(255),
	@patientFirstName varchar(255),
	@patientLastName varchar(255),
	@irow_number int
)
As 

declare	@sql nvarchar(4000),
	@Paramdefinition nvarchar(1000)

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

SELECT @sql = N'
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		myResults.sub_categoryID in (' + @AccessCode + ')'

IF @patientHospNo > ''
	SET @sql = @sql + ' AND patientHospNo = ' + quotename(@patientHospNo, '''')

IF @patientFirstName > ''
	SET @sql = @sql + ' AND patientFirstName = ' + quotename(@patientFirstName, '''')

IF @patientLastName > ''
	SET @sql = @sql + ' AND patientLastName = ' + quotename(@patientLastName, '''')

set	@sql = @sql + ') as d '

SeT @sql = @sql + 'WHERE RowNumber > @row_number'

SET @Paramdefinition =N'@row_number int'

exec sp_executesql @sql, @Paramdefinition, @row_number = @irow_number

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-28 : 05:47:38

quote:

Originally posted by Peso

You can't use the alias name ROWNUMBER within same scope to check if the value is greater than something else.
Make the query a servied table.

ALTER PROCEDURE	sp_find_patients
(
	@AccessCode varchar(500),
	@patientHospNo varchar(255),
	@patientFirstName varchar(255),
	@patientLastName varchar(255),
	@irow_number int
)
As 

declare	@sql nvarchar(4000),
	@Paramdefinition nvarchar(1000)

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

SELECT @sql = N'
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		myResults.sub_categoryID in (' + @AccessCode + ')'

IF @patientHospNo > ''
	SET @sql = @sql + ' AND patientHospNo = ' + quotename(@patientHospNo, '''')

IF @patientFirstName > ''
	SET @sql = @sql + ' AND patientFirstName = ' + quotename(@patientFirstName, '''')

IF @patientLastName > ''
	SET @sql = @sql + ' AND patientLastName = ' + quotename(@patientLastName, '''')

set	@sql = @sql + ') as d '

SeT @sql = @sql + 'WHERE RowNumber > @row_number'

SET @Paramdefinition =N'@row_number int'

exec sp_executesql @sql, @Paramdefinition, @row_number = @irow_number

E 12°55'05.25"
N 56°04'39.16"

there's already one alias

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 05:52:26

Why is then all the WHERE outside?
ROW_NUMBER() will get a number for all records, then you filter out patient name (as an example)
and then you try to match that the remaining rownumber value is greater than an arbitrary number?

Makes no sense to me.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 05:53:23

And there is no need for dynamic SQL at all here

ALTER PROCEDURE	sp_find_patients
(
	@AccessCode varchar(500),
	@patientHospNo varchar(255),
	@patientFirstName varchar(255),
	@patientLastName varchar(255),
	@irow_number int
)
As 

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
		INNER JOIN	dbo.fnMySplitFunction(@AccessCode) AS x ON x.splitvalue = myResults.sub_categoryID
		WHERE		(patientHospNo = @patientHospNo or @patientHospNo = '')
				AND (patientFirstName = @patientFirstName or @patientFirstName = '')
				AND (patientLastName = @patientLastName or @patientLastName = '')
	) as d
WHERE	RowNumber > @irow_number

E 12°55'05.25"
N 56°04'39.16"

robson
Starting Member

22 Posts

Posted - 2008-04-28 : 06:05:49

Hi Everyone,

Thanks for all the help.

I tried to user the following :

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 nvarchar(4000),@Paramdefinition nvarchar(1000)

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

SELECT @sql = N'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 myResults.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'
SET @Paramdefinition =N'@row_number int'

exec sp_executesql @sql,@Paramdefinition,@row_number=@irow_number

It said, that the column 'sub_categoryID' was specified multiple times for 'myResults'.

When I try to use:

ALTER PROCEDURE sp_find_patients
(
@AccessCode varchar(500),
@patientHospNo varchar(255),
@patientFirstName varchar(255),
@patientLastName varchar(255),
@irow_number int
)
As

declare @sql nvarchar(4000),
@Paramdefinition nvarchar(1000)

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

SELECT @sql = N'
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 myResults.sub_categoryID in (' + @AccessCode + ')'

IF @patientHospNo > ''
SET @sql = @sql + ' AND patientHospNo = ' + quotename(@patientHospNo, '''')

IF @patientFirstName > ''
SET @sql = @sql + ' AND patientFirstName = ' + quotename(@patientFirstName, '''')

IF @patientLastName > ''
SET @sql = @sql + ' AND patientLastName = ' + quotename(@patientLastName, '''')

set @sql = @sql + ') as d '

SeT @sql = @sql + 'WHERE RowNumber > @row_number'

SET @Paramdefinition =N'@row_number int'

exec sp_executesql @sql, @Paramdefinition, @row_number = @irow_number

On the asp page it says:
Incorrect syntax near ')'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-28 : 07:01:44

You have multiple columns with same name. Make use of alias.
Better is to not use * to get records.

E 12°55'05.25"
N 56°04'39.16"

robson
Starting Member

22 Posts

Posted - 2008-04-29 : 03:44:51

I managed to sort this out in the end by using alises.

Thanks everyone.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-29 : 05:12:55