1

I have this code:

using (OleDbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = string.Format(
        @"SELECT TOP {0} t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'", CountToFetch);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@firstId", FirstId);

...but wonder if I could use a param for the top count too, like:

using (OleDbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = 
        @"SELECT TOP @count t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'";
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@firstId", FirstId);
    cmd.Parameters.AddWithValue("@count", CountToFetch);

...or are database params limited to the WHERE clause only?

UPDATE

With this code:

  cmd.CommandText = 
        @"SELECT TOP @countToFetch t_accounts.account_no as AccountID, 
    IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = 
    td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'";
    . . .
  cmd.Parameters.AddWithValue("@firstId", FirstId);
  cmd.Parameters.AddWithValue("@countToFetch", CountToFetch);

...I got, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

So I reverted back to:

   cmd.CommandText = string.Format(
        @"SELECT TOP {0} t_accounts.account_no as AccountID, 
    IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
        FROM t_accounts 
        INNER JOIN td_department_accounts ON (t_accounts.account_no = 
    td_department_accounts.account_no) 
        WHERE (AccountID >= @firstId) AND type = 'DE'", CountToFetch);
    . . .
    cmd.Parameters.AddWithValue("@firstId", FirstId);

UPDATE 2

This:

SELECT TOP (@countToFetch) t_accounts.account_no as AccountID, IIF(ISNULL
(t_accounts.name),'[blank]',t_accounts.name) AS Name 
FROM t_accounts 
INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
WHERE (AccountID >= @firstId) AND type = 'DE'"

...in Access tells me "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

NOTE: Postman tells me the exact same thing when making the REST call that ends up with the making of that query.

UPDATE 3

I also tried it as in Update 2 but with ":" instead of "@" and this way:

SELECT TOP (?) t_accounts.account_no as AccountID, IIF(ISNULL(t_accounts.name),'[blank]',t_accounts.name) AS Name 
FROM t_accounts 
INNER JOIN td_department_accounts ON (t_accounts.account_no = td_department_accounts.account_no) 
WHERE (AccountID >= ?) AND type = 'DE'"
B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    possible duplicate of [Use variable with TOP in select statement in SQL Server without making it dynamic](http://stackoverflow.com/questions/1927450/use-variable-with-top-in-select-statement-in-sql-server-without-making-it-dynami) – Matthew Haugen Jul 15 '14 at 23:45

1 Answers1

1

I came across this post that seems to indicate that this does work, at least with T-SQL. I'm inclined to assume, given that, that it works with Access. Just, as that says, remember to include parentheses.

In any event, it's definitely not true that parameters can only be in the WHERE clause. You can use them in the SELECT, or even the ORDER BY.

Matthew Haugen
  • 12,916
  • 5
  • 38
  • 54
  • I got, "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." – B. Clay Shannon-B. Crow Raven Jul 17 '14 at 22:49
  • 1
    That post said to include the parameter in parentheses: `SELECT TOP (@countToFetch) t_accounts...`. Does that work better at all? – Matthew Haugen Jul 17 '14 at 22:53
  • Not compiling presently, but I'll give that a try. – B. Clay Shannon-B. Crow Raven Jul 17 '14 at 23:05
  • 1
    Sounds good. You might also want to try it in Access itself. Sometimes that can lend valuable insight for no apparent reason. – Matthew Haugen Jul 17 '14 at 23:10
  • 1
    Interesting. Well it's certainly possible that it's just not doable in Access. That's somewhat surprising, but I guess there have to be differences in some places between it and T-SQL. Does Access have syntax diagrams like T-SQL does? That would be a definitive source, I suspect. I just looked a bit but I can only find the more "user-friendly" pages on Office that don't actually say anything useful. – Matthew Haugen Jul 17 '14 at 23:39
  • I'm wondering if the "@" is what Access doesn't recognize, but I don't know if it would expect "?" or ":" or something else. – B. Clay Shannon-B. Crow Raven Jul 17 '14 at 23:41
  • 1
    I would expect the syntax to be the same as it is for other parameters, though. I mean, it's certainly an interesting question. I would never have guessed or figured out that SQL requires those parentheses around it without looking it up, so it's definitely possible that it's some totally random indicator that it's looking for. – Matthew Haugen Jul 17 '14 at 23:49