I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.
-- BTW I am on the September CTP with VS 2005 RC
Thanks,
Adam
Error Message
Msg 8624, Level 16, State 1, Line 2
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.
Below is the SQL I am using to test
declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)
Function Definition
CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO
Source Code
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}
[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sVals.pos = i + 1;
sVals.value = vals
;
}
return sVals;
}
public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.
No comments:
Post a Comment