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

namespace TestSQLCLRFunction
{
    public class TestSQLCLRFunction
    {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
        public static SqlString GetIds(SqlString tableName, SqlInt32 department)
        {
            string
                result = string.Format("tableName: \"{0}\", department: {1} (SqlContext.Pipe{2}=null)", tableName, department, SqlContext.Pipe != null ? "!" : "=");

            StringBuilder
                sb = new StringBuilder();

            if(SqlContext.Pipe!=null)
                SqlContext.Pipe.Send(string.Format("tableName: \"{0}\", department: {1}", tableName, department));

            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("select id from {0} where dep=@dep", tableName), conn))
                {
                    try
                    {
                        conn.Open();
                        result += " " + string.Format("SqlConnection.State = \"{0}\"", conn.State);

                        cmd.Parameters.Add("@dep", SqlDbType.Int).SqlValue = department;

                        result += " " + cmd.CommandText + string.Format(" \"{0}\" = {1} ({2})", cmd.Parameters[0].ParameterName, cmd.Parameters[0].SqlValue, cmd.Parameters[0].SqlDbType);

                        result += " before SqlCommand.ExecuteReader()";
                        /*using (*/
                        SqlDataReader rdr = cmd.ExecuteReader();/*)*/
                        result += " after SqlCommand.ExecuteReader()";
                        /*{*/
                            result += " " + string.Format("SqlDataReader.HasRows = {0}", rdr.HasRows);

                            while (rdr.Read())
                                sb.Append(string.Format(",{0}", rdr.GetSqlInt64(0)));

                            rdr.Close();
                        /*}*/

                        result += " after";
                    }
                    catch (Exception eException)
                    {
                        if (SqlContext.Pipe != null)
                            SqlContext.Pipe.Send(result = "Error executing SQL statement: " + eException.Message);
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            return new SqlString(/*!string.IsNullOrEmpty(result = sb.ToString()) ? result.Substring(1) : */result);
        }
    }
}

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

namespace TestSQLCLRFunction
{
    public class TestSQLCLRFunction
    {
        [SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]
        public static SqlString GetIds(SqlString tableName, SqlInt32 department)
        {
            string
                result;

            StringBuilder
                sb = new StringBuilder();

            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                using (SqlCommand cmd = new SqlCommand(string.Format("select id from {0} where dep=@dep", tableName), conn))
                {
                    conn.Open();
                    cmd.Parameters.AddWithValue("@dep", department);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                            sb.Append(string.Format(",{0}", rdr.GetSqlInt64(0)));
                    }
                }
            }

            return new SqlString(!string.IsNullOrEmpty(result = sb.ToString()) ? result.Substring(1) : result);
        }
    }
}