Trying to parameterize the value of TOP in my sql statement.
SELECT TOP @topparam * from table1
command.Parameters.Add("@topparam",SqlDbType.VarChar, 10).Value = somevalue.ToString();
This doesn't seem to work. Anyone have any suggestions?
Just to clarify, I don't want to use stored procedures.
In SQL Server 2005 and above, you can do this:
SELECT TOP (@topparam) * from table1
You need to have at least SQL Server 2005. This code works fine in 2005/8 for example ...
DECLARE @iNum INT
SET @iNum = 10
SELECT TOP (@iNum) TableColumnID
FROM TableName
If you have SQL Server 2000, give this a try ...
CREATE PROCEDURE TopNRecords
@intTop INTEGER
AS
SET ROWCOUNT @intTop
SELECT * FROM SomeTable
SET ROWCOUNT 0
GO
You could write an inline query:
EXEC 'SELECT TOP ' + @topparam + ' * FROM ... '
Parse it as an int and that will prevent a SQL injection attack.
