SQLCLR stored procedures with input parameter

I am quite new to SQLCLR stored procedures. In my example I have two stored procedures, one without a parameter and one with a input parameter. Both target the same tables.

The one without the parameter is working fine and returns all rows in the result. But the one with an input parameter targeting the same tables is not returning any rows even though I am not receiving any errors. The input parameter in the .NET code is set as SqlString and in the database is NVARCHAR(50).

This is how my C# code looks:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AirlineSqlStoredProcedure (SqlString strAirline)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Context Connection=true";

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        conn.Open();
        cmd.CommandText = "SELECT  dbo.tblAirline.AirlineName, dbo.tblAircraft.AircraftUnits, dbo.tblAircraft.Manufacturer, dbo.tblAircraft.AircraftModel FROM dbo.tblAircraft INNER JOIN dbo.tblAirline ON dbo.tblAircraft.AirlineID = dbo.tblAirline.AirlineID  WHERE AirlineName = '@strAirline' ORDER BY dbo.tblAircraft.AircraftUnits DESC";
        SqlParameter paramAge = new SqlParameter();
        paramAge.Value = strAirline;
        paramAge.Direction = ParameterDirection.Input;
        paramAge.SqlDbType = SqlDbType.NVarChar;
        paramAge.ParameterName = "@strAirline";

        cmd.Parameters.Add(paramAge);
        SqlDataReader sqldr = cmd.ExecuteReader();
        SqlContext.Pipe.Send(sqldr);

        sqldr.Close();
        conn.Close();
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AirlineAircraftStoredProcedure()
    {
        //It returns rows from Roles table    
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Context Connection=true";

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "SELECT  dbo.tblAirline.AirlineName, dbo.tblAircraft.AircraftUnits, dbo.tblAircraft.Manufacturer, dbo.tblAircraft.AircraftModel FROM dbo.tblAircraft INNER JOIN dbo.tblAirline ON dbo.tblAircraft.AirlineID = dbo.tblAirline.AirlineID  ORDER BY dbo.tblAircraft.AircraftUnits DESC";
        conn.Open();

        SqlDataReader sqldr = cmd.ExecuteReader();
        SqlContext.Pipe.Send(sqldr);

        sqldr.Close();
        conn.Close();

    }
}

And when I execute the stored procedure I get empty rows:

USE [TravelSight]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[AirlineSqlStoredProcedure]
        @strAirline = N'American Airlines'

SELECT  @return_value as 'Return Value'

GO

(0 row(s) affected)

(1 row(s) affected)

Also, for the input parameter I put an N before the string.

When running the stored procedure AirlineAircraftStoredProcedure targeting the same tables, I am getting all the rows back:

USE [TravelSight]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[AirlineAircraftStoredProcedure]

SELECT  @return_value as 'Return Value'

GO


(8 row(s) affected)


(1 row(s) affected)

What have I done wrong here?

Solution

Two (maybe 3) problems:

paramAge.Value = strAirline; should be:

paramAge.Value = strAirline.Value;

Notice the use of the .Value property.
WHERE AirlineName = '@strAirline' (within cmd.CommandText = "... ) should be:

WHERE AirlineName = @strAirline

Notice that the single-quotes were removed in the query text. You only use single-quotes for literals and not parameters / variables.

Replace the following 5 lines:

SqlParameter paramAge = new SqlParameter();
paramAge.Value = strAirline;
paramAge.Direction = ParameterDirection.Input;
paramAge.SqlDbType = SqlDbType.NVarChar;
paramAge.ParameterName = "@strAirline";

with:

SqlParameter paramAge = new SqlParameter("@strAirline", SqlDbType.NVarChar, 50);
paramAge.Direction = ParameterDirection.Input; // optional as it is the default
paramAge.Value = strAirline.Value;

Please note that the "size" parameter was set in the call to new SqlParameter(). It is important to always specify max string lengths.

With the technical problem out of the way, there are two larger issues to address:

Why is this being done in SQLCLR in the first place? Nothing specific to .NET is being done. Based solely on the code posted in the Question, this would be much better off as a regular T-SQL Stored Procedure.
If it must remain in SQLCLR, then you really need to wrap the disposable objects in using() constructs, namely: SqlConnection, SqlCommand, and SqlDataReader. For example:
```
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
  using (SqlCommand cmd = conn.CreateCommand())
  {
    ...
  }
}
```
and then you do not need the following two lines:
```
sqldr.Close();
conn.Close();
```
as they will be called implicitly by the call to each of their Dispose() methods.