C# returns error 'Subquery returned more than 1 value' but that should be ok

Question

I am trying to link two combo boxes in a C# winform app.

The first combobox - comboKategorija, once selected with the _SelectedIndexChanged sends the selected value from the combobox as an argument to a stored procedure.

private void comboKategorija_SelectedIndexChanged(object sender, EventArgs e)
{
    DataModel db = new DataModel();
    CheckInternet net = new CheckInternet();

    if (net.Available())
    {
        var validated = db.ChangeComboSifra(comboKategorija.Text);

        comboSifra.DataSource = validated;
        comboSifra.DisplayMember = "prefiks";
        comboSifra.ValueMember = "prefiks";
    }
    else
    {
        MessageBox.Show("Интернет конекцијата е недостапна.", "Информација", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
    }    
}

I use dapper. The method is below. I pass only one argument - the selected value from the combox.

public List<Sifrarnik> ChangeComboSifra(string opis)
{
    using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["Cloud"].ConnectionString))
    {
        if (db.State == ConnectionState.Closed)
        {
            db.Open();
        }

        var response = db.Query<Sifrarnik>("dbo.spChangeComboSifra @opis",
            new
            {
                opis = opis
            }).ToList();
        return response;
    }
}

The stored procedure is support to return one or multiple rows, which will prepopulate the second combobox - comboboxSifra

ALTER PROCEDURE [dbo].[spChangeComboSifra]    
    @opis nvarchar(300)    
AS
BEGIN    
    DECLARE @MyPrefiks nvarchar(100)    
    SET @MyPrefiks = (SELECT prefiks from Sifrarnik where opis LIKE '%' + @opis + '%')
    SELECT prefiks,opis FROM Sifrarnik where SUBSTRING(prefiks, 1,2) = @MyPrefiks  and LEN(prefiks) > 2
END

However I get the following error:

System.Data.SqlClient.SqlException: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.'

It says more than one value returned, which is exactly what I want. But I'm not sure why the error?

The error is being causes by this line in your SP `SET @MyPrefiks = (SELECT prefiks from Sifrarnik where opis LIKE '%' + @opis + '%')` - you can't assign more than 1 value to a variable. — Dale K, Sep 15 '19 at 21:58
"return one or multiple rows" so you need to return that set of one or more rows and put that into a list perhaps — Mark Schultheiss, Sep 15 '19 at 22:13

score 2 · Accepted Answer · answered Sep 15 '19 at 22:06

There is more than one Sifrarnik that has an opis matching whatever you're passing in the @opis variable:

SET @MyPrefiks = (SELECT prefiks from Sifrarnik where opis LIKE '%' + @opis + '%')

If this query matches 2+ rows, the execution of the SP will halt with an error. Decide how you want to ensure that this query returns only a single row. You could:

SET @MyPrefiks = (SELECT MAX(prefiks) from Sifrarnik where opis LIKE '%' + @opis + '%')

SET @MyPrefiks = (SELECT TOP 1 prefiks from Sifrarnik where opis LIKE '%' + @opis + '%' ORDER BY ...)

And so on

If the query returns multiple rows but you genuinely want to use all of them you'll have to use its output to drive the query that @MyPrefiks formerly used, for example:

SELECT prefiks,opis 
FROM Sifrarnik 
where 
  SUBSTRING(prefiks, 1,2) IN (SELECT prefiks from Sifrarnik where opis LIKE '%' + @opis + '%') 
  and LEN(prefiks) > 2

score 1 · Answer 2 · answered Sep 15 '19 at 23:10

The issue is that SET only works if you return zero or one rows. SELECT, on the other hand, works fine regardless of the number of the rows returned (although only one of the values will be assigned). As such, you could use:

SELECT @MyPrefiks = prefiks from Sifrarnik where opis LIKE '%' + @opis + '%'

Note that SET and SELECT will act differently if the query returns no rows (SET will set it the variable to NULL while SELECT will leave it unchanged).

C# returns error 'Subquery returned more than 1 value' but that should be ok

2 Answers2