We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.
Code Snippet
[
SqlFunction ( FillRowMethodName = "FillRow",TableDefinition =
"CustomerID nvarchar(MAX)",SystemDataAccess =
SystemDataAccessKind.Read,DataAccess =
DataAccessKind.Read,IsDeterministic=
false)] public static IEnumerable GetWishlist () { using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) { List<string> myList = new List<string> ();conn.Open ();
SqlCommand command = conn.CreateCommand ();command.CommandText =
"GetObject";command.Parameters.AddWithValue (
"@map", "Item" );command.CommandType = System.Data.
CommandType.StoredProcedure; using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) { if (reader.Read ()) {myList.Add ( reader[0]
as string );}
}
return (IEnumerable)myList;}
}
When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.
Code Snippet
EXEC GetObject 'Item'
Is there some sorf of trick I am missing
Thank you!