ScriptDOM Succinctly®
CHAPTER 11
Functions
SQL has a large number of functions, such as UPPER(), TRIM(), and CHARINDEX(). We can use ScriptDOM to find the functions being used in the code. In this chapter, we will look at functions found within a stored procedure.
We recently had a custom-written string_split() function (since we are using a version of SQL prior to 2016). However, due to recursion, this version was limited to 100 rows. We need to find all references to the custom-written version so we can update it to the SQL 2016 built-in version.
We can set up a Visitor class to catch all function calls, as shown in Listing 44.
Code Listing 44: Function Call visitor
public class Visitor : TSqlFragmentVisitor { public override void ExplicitVisit(FunctionCall node) {
base.ExplicitVisit(node); } } |
The key properties of the FunctionCall class are shown in Table 27.
Table 27: Function Call properties
Property | Type | Description |
|---|---|---|
FunctionName | Identifier | The Value property contains the function name |
Parameters | Collection of parameters passed to the function | · Function call (nested function call) · Column reference expression · String literal · Numeric literal |
CallTarget | Multi-part identifier call target | If the function is a user-defined function, this property will not be null. You can iterate the identifiers to build the schema containing the function. |
Identifying types of functions
SQL has a number of built-in functions, so we can write a visitor method that will flag each function call as SQL or a UDF. In our case, we want to make sure we use the native SQL version of String_Split(), not the UDF version.
Listing 45 shows the code to report the type of function, either SQL or UDF.
Code Listing 45: Categorize function calls
public override void ExplicitVisit(FunctionCall node) { string fnName = ""; string schema = ""; Identifier? fn = node.FunctionName as Identifier; if (fn != null) { fnName = fn.Value; } // This will be set for user-defined functions CallTarget? ct = node.CallTarget as CallTarget; if (ct != null && ct is MultiPartIdentifierCallTarget) { MultiPartIdentifierCallTarget ctm = (MultiPartIdentifierCallTarget)ct; string ans = ""; for (int x = 0; x < ctm.MultiPartIdentifier.Identifiers.Count; x++) { if (x > 0) { ans += "."; } ans += ctm.MultiPartIdentifier.Identifiers[x].Value; } schema = ans; } if (schema == "") { Console.WriteLine("SQL " + fnName); } Else { Console.WriteLine("UDF " + schema+"."+fnName); } base.ExplicitVisit(node); } |
Reporting function errors
As an example of a function error we can look for, we can use the Format() function. A common mistake I’ve seen is not understanding the case-sensitivity of the function. For example, if I want to display the current date formatted, I would use MM/dd/yyyy. Table 28 shows a couple of example date formats and what they return.
Table 28: Date formats
Format | Returns | Notes |
MM/dd/yyyy | 08/25/2023 | This is good! |
mm/dd/yyyy | 12/25/2023 | Lower case mm is minutes, not month |
MM/DD/YYYY | 08/DD/YYYY | DD and YYYY are invalid |
MM/dd/YYYY | 08/25/YYYY | YYYY is invalid |
We can use the visitor pattern to report potential date format errors. Listing 46 shows code to potentially catch incorrect format values.
Code Listing 46: Check date format values
if (fnName.ToUpper()=="FORMAT" && node.Parameters.Count>1) { StringLiteral? fmt = node.Parameters[1] as StringLiteral; if (fmt != null) { string fmtString = fmt.Value; if (fmtString.StartsWith("mm/")) { Console.WriteLine("Check date format ("+fmtString+"), mm is for minutes, MM is for months"); } if (fmtString.EndsWith("/YYYY") || fmtString.EndsWith("/YY")) { Console.WriteLine("Check date format ("+fmtString+"), YYYY or YY for year must be lower case"); } } } |
Some other ideas
You can use this visitor to look for other types of errors, particularly subtle ones that might slip through. Table 29 offers a few more suggested patterns to look for.
Table 29: Possible function problems
Function name | Potential issue |
|---|---|
CharIndex() | If the first string parameters contain a wildcard character (such as % or *), suggest using PatIndex() instead |
Nested Replace() functions | Replace code like replace(replace(Replace('[1&2]', '[','('),']',')'),'&','+') with TRANSLATE('[1&2]','[&]','(+)') |
HashBytes() | Replace the deprecated hash algorithm (MD2, MD4, MD5, SHA, SHA1) with the more secure SHA2_256 or SHA2_512 algorithm |
@@Identity | Replace @@identity with Scope_Identity() to prevent triggers from impacting the expected identity key |
Hopefully, this list will provide you with some ideas of functions you might want to review in your code. Each of these examples shows valid function calls, but unexpected behavior could surprise a SQL developer. As another example, you might want to set a standard way to present dates when using the Format() function.
Summary
The function call visitor allows you to easily find user-defined functions in your code, as well as suggest potential problems with the functions. This came in very handy for my company to find the String_Split() user-defined functions and replace them with the native SQL version.
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.