How to define a limit as parameter?

Question

Given conn is an OdbcConnection object and count is an int, how would I use count as parameter for my query?

...
var query = conn.CreateCommand();
query.CommandText = "select top ? * from players order by Points desc";
query.Parameters.Add("top", OdbcType.Int).Value = count;

var reader = query.ExecuteReader();
while (reader.Read())
{
    ...
}
...

This way I get a syntax error ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P1'.

If it is not possible the way I tried how would I do it the correct way instead?

top isn't the parameter, you should have something like `@Counter` in your query and add that as a parameter. I.e. `query.Parameters.Add("@Counter", OdbcType.Int).Value = count` — Darren, Mar 07 '14 at 16:16
@user2525463 - you shouldn't concatenate strings, using parameters for queries is the correct way as it prevents SQL Injection. — Darren, Mar 07 '14 at 16:17
@DarrenDavies i know top is not the parameter but how would I name a parameter as count for top? — matthias krull, Mar 07 '14 at 16:19
possible duplicate of [SQL Server - use a parameter to select the top X of the result set](http://stackoverflow.com/questions/6285394/sql-server-use-a-parameter-to-select-the-top-x-of-the-result-set) — maf748, Mar 07 '14 at 16:21
@matthiaskrull, any particular reason that you are using `OdbcConnection` and now `SqlConnection` ?? — Habib, Mar 07 '14 at 16:22
@Habib No, I was using odbc with other languages and just looked it up. I am just doing quick and dirty experimenting and could switch to something else as well at this point. — matthias krull, Mar 07 '14 at 16:25
@maf748 not a duplicate since the question asks specifically how to get that behaviour with `ODBC` which is different from `SqlConnection` for example and is not necessarily the same as raw `T-SQL` — matthias krull, Mar 10 '14 at 09:02

Habib · Answer 1 · 2014-03-07T16:38:57.557

2

You can do:

query.CommandText = "select top (@topparameter) * from players order by Points desc";
query.Parameters.AddWithValue("@topparameter", count.ToString());

If you are using SqlServer then use SqlConnection and SqlCommand like:

using (SqlConnection conn = new SqlConnection("YourConnectionString"))
{
    using (SqlCommand query = new SqlCommand("select top (@topparameter) * from players order by Points desc", conn))
    {
        query.Parameters.AddWithValue("@topparameter", count.ToString());
        var reader = query.ExecuteReader();
        while (reader.Read())
        {
        }
    }
}

edited Mar 07 '14 at 16:38

answered Mar 07 '14 at 16:19

Habib

219,104
29
407
436

This yields `ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@topparameter"`. – matthias krull Mar 07 '14 at 16:22
@matthiaskrull, modified my answer with respect to SQL server, see if it works now. – Habib Mar 07 '14 at 16:39
T-SQL top command don't accepts a variable as parameter, you can do that way if you use a dynamic query. To avoid dynamic queries use set rowcount, se my answer below. – jean Mar 07 '14 at 17:08
Using `SqlConnection` it works passing parameters this way. Thank you for your answer. – matthias krull Mar 10 '14 at 09:03

score 2 · Accepted Answer · answered Mar 07 '14 at 17:03

2

You can also use SET ROWCOUNT the advantage is you can use a integer as parameter and avoid dynamic queries.

SET ROWCOUNT @top;

select * from table;

SET ROWCOUNT 0;

read the documentation

answered Mar 07 '14 at 17:03

jean

4,159
4
31
52

How to define a limit as parameter?

2 Answers2