New "Business rule" like type to create table valued /scalar functions

1

Hi,

while we can create business rules which act similar to table valued / scalar functions, we can't pass the columns/data to them. The inner business rules are executed before the outer SQL command is executed. This comes in really handy in a lot of cases but also has it's limitation and will require creating repetitive code.

I would suggest the following:

Create a new "type" "SQL function"

Properties would be similar to a business rule:
Name : GetFirstTextPart
Description: Will split the text by the line break character and return the first part of the text. If no line break character is present, the input will be returned.
SQL Command
CASE
WHEN CHARINDEX(CHAR(10), {Parameter:1}) > 0 THEN LEFT({Parameter:1}, CHARINDEX(CHAR(10), {Parameter:1}) - 1)
ELSE {Parameter:1}
Number of rows to return: First row (scalar function), all rows (table valued)
Parameters:
Name: Input
Description: The complete text with the line break character.

When this is saved a function scalar/table valued is created and the bps_user gains execution permissions.

CREATE FUNCTION dbo.GetTextPartEN(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
RETURN
CASE
WHEN CHARINDEX(CHAR(10), @input) > 0 THEN LEFT(@input, CHARINDEX(CHAR(10), @input) - 1)
ELSE @input
END
END


You could then use the variable of this new type like the other business rules
select *, {SQLFunction:1700:} as DescriptionEN
, {SQLFunction:1700:} as RemarkEN

Which would resolve to
select *, dbo.GetTextPartEN(Description) as DescriptionEN,dbo.GetTextPartEN(Remark) as RemarkEN

This came only to my mind now, because I'm creating a lot of "repetitive statements" in a few data tables, but when I think back, it would have helped me in a lot of other cases two.

Best regards,
Daniel