simplifying non-parametrized mysql queries

Question

I have a problem with mySql query below

SELECT name, hospitalID, currentAvgRating, rank 
FROM ( SELECT name,hospitalID,currentAvgRating,city,
              @curRank := @curRank + 1 AS rank 
       FROM hospitals h, ( SELECT @curRank := 0) r 
       ORDER BY currentAvgRating DESC
      ) toplist 
WHERE toplist.hospitalID = @hospitalID 
  and city = @city

This query I am using to find the rank of particular item & it is working properly. But while runining in program I get Fatal errors of Parameter '@curRank' must be defined. But that is mysql syntax then how can I get it's parameters?

UPDATE

string str = "SELECT name, hospitalID, currentAvgRating, rank FROM (SELECT name,hospitalID,currentAvgRating,city,@curRank := @curRank + 1 AS rank FROM hospitals h, (SELECT @curRank := 0) r ORDER BY currentAvgRating DESC) toplist WHERE toplist.hospitalID = @hospitalID and city = @city";
con.Open();
MySqlCommand cmd = new MySqlCommand(str, con);
cmd.Parameters.AddWithValue("@hospitalID", generalID.Text);
cmd.Parameters.AddWithValue("@city", cityName.Text);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
con.Close();

Why don't you show the C# code that call this query? – Steve Jul 30 '16 at 13:54 — Steve, Jul 30 '16 at 13:54

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

You can get the rank for a particular hospital/city pair without a rank. Here is a close approximation to your query:

select count(*) + 1 as ranking
from hospitals h cross join
     (select h.currentAvgRating
      from hospitals h
      where h.hospitalID = @hospitalID and h.city = @city
     ) hh
where h.currentAvgRating > hh.currentAvgRating;

Unlike your code, this gives all hospitals with the same rating, the same ranking.

If you don't want to change the code, then refer to this answer. Actually, I'll quote the relevant part:

I have to add
;Allow User Variables=True
to the connection string

edited May 23 '17 at 12:14

Community

1
1

answered Jul 30 '16 at 13:56

Gordon Linoff

1,242,037
58
646
786

Thanks. But my sql query is working properly as I want. Anyways adding Allow User Variables=True solved my problem so thanks. – SUN Jul 30 '16 at 14:00

score 0 · Answer 2 · answered Jul 30 '16 at 14:13

Your SQL is correct I think there are conflect with C# command parameter and mySQL parameter

try this modification of SQL code like this

SELECT name, hospitalID, currentAvgRating, rank 
FROM ( SELECT name,hospitalID,currentAvgRating,city,
              @curRank := @curRank + 1 AS rank 
       FROM hospitals h, ( SELECT @curRank := 0) r 
       ORDER BY currentAvgRating DESC
      ) toplist 
WHERE toplist.hospitalID = ?hospitalID 
  and city = ?city

you c# like this

   string str = <Example Above>;
    con.Open();
    MySqlCommand cmd = new MySqlCommand(str, con);
    cmd.Parameters.AddWithValue("?hospitalID", generalID.Text);
    cmd.Parameters.AddWithValue("?city", cityName.Text);
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt); 
    con.Close();

Just changeing the C# command parameter using ? than using @

simplifying non-parametrized mysql queries

2 Answers2