I have a stored procedure that accepts a string and extracts substrings that starts with '@'. I decided to use C# DLL for this and access this DLL using SQL Assemblies. So, I developed following method in C#:
我有一个存储过程接受一个字符串并提取以'@'开头的子字符串。我决定使用C#DLL,并使用SQL程序集访问此DLL。所以,我在C#中开发了以下方法:
[Microsoft.SqlServer.Server.SqlFunction]
public static ? GetSubStrings(string text)
{
List<string> IDs=new List<string>();
foreach (Match match in Regex.Matches(text, @"(?<!\w)@\w+"))
{
try
{
IDs.Add(match.Value.Replace("@", ""));
}
catch (NullReferenceException) { continue; }
}
return IDs;
}
I added '?' sign in place of return type because I don't know what return type this method should return? Please tell me the solution. I am unable to find any SQL type that stores this kind of data.
我加了'?'签到返回类型,因为我不知道这个方法应返回什么返回类型?请告诉我解决方案。我无法找到存储此类数据的任何SQL类型。
1 个解决方案
#1
To do this you need to make your function a Table Valued Function, this will make your function return a table instead of a single result. To do this you need to use the TableDefinition property and have your return type be a IEnumerable.
要做到这一点,你需要使你的函数成为表值函数,这将使你的函数返回一个表而不是一个结果。为此,您需要使用TableDefinition属性并使您的返回类型为IEnumerable。
[Microsoft.SqlServer.Server.SqlFunction(TableDefinition="IDs nvarchar(max)"]
public static IEnumerable GetSubStrings(string text)
{
List<string> IDs=new List<string>();
foreach (Match match in Regex.Matches(text, @"(?<!\w)@\w+"))
{
try
{
IDs.Add(match.Value.Replace("@", ""));
}
catch (NullReferenceException) { continue; }
}
return IDs;
}
#1
To do this you need to make your function a Table Valued Function, this will make your function return a table instead of a single result. To do this you need to use the TableDefinition property and have your return type be a IEnumerable.
要做到这一点,你需要使你的函数成为表值函数,这将使你的函数返回一个表而不是一个结果。为此,您需要使用TableDefinition属性并使您的返回类型为IEnumerable。
[Microsoft.SqlServer.Server.SqlFunction(TableDefinition="IDs nvarchar(max)"]
public static IEnumerable GetSubStrings(string text)
{
List<string> IDs=new List<string>();
foreach (Match match in Regex.Matches(text, @"(?<!\w)@\w+"))
{
try
{
IDs.Add(match.Value.Replace("@", ""));
}
catch (NullReferenceException) { continue; }
}
return IDs;
}