Stored procedure

[vlitim]

below is a part of a stored procedure I am trying to get to work but it won't accept the @NumberRecs in the select statement. Any reason??

CREATE PROCEDURE sp_Top5ReadWriters
(
@NumberRecs int
)
AS

SELECT TOP @NumberRecs contributor,hits
FROM #UserList
WHERE contributor != 5
Order by hits DESC

 

[linda105]

I don't know if this is the problem, but in my version of SQL server I don't seem to have a TOP function.
To accomplish the same thing though:

SET ROWCOUNT 5
SELECT sample_number
FROM sample
Order by sample_number DESC
SET ROWCOUNT 0

 

[mbryant]

To do this you will need to build the SQL statement into a variable then execute that variable for it to work

declare @SQLStatement varchar(1000)

set @SQLStatement = "
SELECT TOP "+@NumberRecs+" contributor,hits
FROM #UserList
WHERE contributor != 5
Order by hits DESC"

execute(@SQLStatement)


Matt :-V

 

[mukund]

A small change to what Matt has suggested.

Declare @NumberRecs as varchar(10) or convert @NumberRecs to varchar(10) in the @SQLStatement.

set @SQLStatement = "
SELECT TOP "+Convert(varchar(10),@NumberRecs)+" contributor,hits
FROM #UserList
WHERE contributor != 5
Order by hits DESC"

This is to avoid the datatype conversion error when the @SQLStatement is executed.

Mukund.

 

[dbtech]

Lets do The easy way!, Linda105 query works fine, I will replace the 5 with a parameter value.

CREATE PROCEDURE sp_Top5ReadWriters
(
@NumberRecs int
)
AS

SET ROWCOUNT @NumberRecs

SELECT contributor,hits
FROM #UserList
WHERE contributor != 5
Order by hits DESC

SET ROWCOUNT 0

dbtech