CODEWITHSUNDEEP
c#sql-serverinner-join
Ole M
Ole M

Reputation: 347

SQL INNER JOIN, @Parameter - does not filter results

Thank you in advance for any advice you are able to give me.

What I'm trying to achieve: I have a combobox that I want to populate with table1 / columnC, sorted by matches in table1 / columnB and table2 / columnB.

For example: I want to display all of Roberts potatoes, not everyone's potatoes.

I have created a stored procedure for this. It currently looks like this:

CREATE PROCEDURE [dbo].[myProcedure]
    @myParameter nvarchar
AS
    SELECT columnA, columnB, columnC
    FROM table1
    INNER JOIN table2 ON table1.columnB = table2.columnB
    WHERE table1.columnB = @myParameter

Data set:

DataSet MyDataSet() 
{
   DataSet ds = new DataSet();

   using (SqlConnection con = new SqlConnection(connectionString))
   using (SqlDataAdapter da = new SqlDataAdapater("myProcedure", con)) 
   {
      da.SelectCommand.CommandType = CommandType.StoredProcedure;

      //the line below gives value to my parameter, which in this case is a WinForms label
      //with the table1/columnB, table2/columnB value (the name that I wish to sort by)
      da.SelectCommand.Parameters.Add("@myParameter", SqlDbType.NVarChar).Value = label.Text.ToString();
      da.Fill(ds);
   }

   return ds;
}

Populating my combobox (code is in form load event):

try 
{
    DataSet ds2 = MyDataSet();
    myComboBox.DataSource = ds2.Tables[0];
    myComboBox.DisplayMember = "columnC";
    myComboBox.ValueMember = "columnA"; // hidden ID row
} 
catch (Exception ex) 
{
    // messageboxcode
}

Oh, and, if it matters, all my columns are nvarchar except for table1/columnA, which is int.

Hope I make sense and that someone is able to give me a hint or two. In regards to what I've tried to fix this, being new to SQL, I have read documentation, watched tutorials and generally spent hours trying to figure this out. I just can't for some reason! :-)

Upvotes: 1

Views: 103

Answers (2)

sgodee
sgodee

Reputation: 1

Add the order like:

    order by table1.columnB,table2.columnB

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270703

I can say that your stored procedure is malformed because you don't have a length on nvarchar(). Never use char and related types in SQL Server without a length parameter.. The default varies by context and assuming the default does what you want just leads to hard to debug errors (ahem).

In the absence of other information, you can use (max), but I would suggest that you use an appropriate value for the length of the parameter:

CREATE PROCEDURE [dbo].myProcedure (
     @myParameter nvarchar(max)
) AS
BEGIN
    SELECT columnA, columnB, columnC
    FROM table1 JOIN
         table2
         ON table1.columnB = table2.columnB
   WHERE table1.columnB = @myParameter;
END;

Upvotes: 1

Related Questions