SQL Server CLR scalar function with default column name

Question

I was wondering if there is a way to add column names to my scalar CLR functions on Sql Server. I mean, after fulfilling my query, I would like to see a column with the result of an already specified function with something custom instead of (No column name) .

I know that often functions are combined together, or I would have to call them something else for another reason, but still - when you write a query with 30 columns, you do not need to break through an alias, of which 20 of them will be pleasant.

Does anyone know a hack that would allow this?

It would also be nice to use this function after some addition to SSMS (for example, creating aliases of dummy functions from functions and columns used in the calculation, for example, "datifart_end_date"). I tried to find a ready-made solution for this, but without effect. Any clues?

Edit: Some people asked me about sample code. I don’t think it helped much, but here it is:

FROM#:

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlTypes; using System.Text.RegularExpressions; namespace ClrFunctions { public class RegexFunctions { public static SqlBoolean clrIsMatch(SqlString strInput, SqlString strPattern) { if (strPattern.IsNull || strInput.IsNull) { return SqlBoolean.False; } return (SqlBoolean)Regex.IsMatch(strInput.Value, strPattern.Value, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant); } } } 

T-SQL:

 Create Assembly ClrFunctions From 'C:\CLR\ClrFunctions.dll' GO Create Function dbo.clrIsMatch( @strInput As nvarchar(4000), @strPattern As nvarchar(255) ) Returns Bit As External Name [ClrFunctions].[ClrFunctions.RegexFunctions].[clrIsMatch] GO 

And this is my desire to be a possible call to this function in T-SQL and the expected result:

 select txt, dbo.clrIsMatch(txt,'[0-9]+') from ( select 'some 123 text' as txt union all select 'no numbers here' union all select 'numbers 456 again' ) x 

The result already has a column name, without the need to add aliases in T-SQL:

Answer 1

The answer is no. A scalar function (CLR or not) simply returns a value.

"Custom scalar functions return a single value of data of the type defined in the RETURNS clause." per MSDN

As you said, the correct solution is to add an alias in which you use this function.

 SELECT ABS(a.Position - b.Position) AS Distance FROM sch.Tbl a INNER JOIN sch.Tbl b ON a.Key <= b.Key ; 

And this does not change whether the function is a built-in or user-defined function, or a CLR function.

Answer 2

I assume you have a call something like this:

 command.CommandText = "SELECT SomeFunction();"; 

try it

 command.CommandText = "SELECT SomeFunction() 'AliasForColumnNameYouWant';"; 

Not sure what you mean by 20 or 30 functions?

Are you saying that Sql-Server has some stored procedure or function that calls 20 or 30 different scalar functions?

Or maybe the same function is called 20 or 30 times?

And do you combine the results in a returned row or data column?

Please use code examples.