SQL/C# - Apply a function to columns within a SQL query

Question

Is there a way to parse a given SQL SELECT query and wrap each column with a function call e.g. dbo.Foo(column_name) prior to running the SQL query?

We have looked into using a regular expression type 'replace' on the column names, however, we cannot seem to account for all the ways in which a SQL query can be written.

An example of the SQL query would be;

SELECT 
    [ColumnA]
  , [ColumnB]
  , [ColumnC] AS [Column C]
  , CAST([ColumnD] AS VARCHAR(11)) AS [Bar]
  , DATEPART([yyyy], GETDATE()) - DATEPART([yyyy], [ColumnD]) AS [Diff]
  , [ColumnE]
FROM [MyTable]
WHERE LEN([ColumnE]) > 0
ORDER BY 
    [ColumnA]
  , DATEPART([yyyy], [ColumnD]) - DATEPART([yyyy], GETDATE());

The result we require would be;

SELECT 
    [dbo].[Foo]([ColumnA])
  , [dbo].[Foo]([ColumnB])
  , [dbo].[Foo]([ColumnC]) AS [Column C]
  , CAST([dbo].[Foo]([ColumnD]) AS VARCHAR(11)) AS [Bar]
  , DATEPART([yyyy], GETDATE()) - DATEPART([yyyy], [dbo].[Foo]([ColumnD])) AS [Diff]
  , [dbo].[Foo]([ColumnE])
FROM [MyTable]
WHERE LEN([dbo].[Foo]([ColumnE])) > 0
ORDER BY 
    [dbo].[Foo]([ColumnA])
  , DATEPART([yyyy], [dbo].[Foo]([ColumnD])) - DATEPART([yyyy], GETDATE());

Any or all of the above columns might need the function called on them (including columns used in the WHERE and ORDER BY) which is why we require a query wide solution.

We have many pre-written queries like the above which need to be updated, which is why a manual update will be difficult.

The above example shows that some result columns might be calculated and some have simply been renamed. Most are also made up with joins and some contain case statements which I have left out for the purpose of this example.

Another scenario which would need to be accounted for is table name aliasing e.g. SELECT t1.ColumnA, t2.ColumnF etc.

Either a SQL or C# solution for solving this problem would be ideal.

Possible duplicate of [Parsing SQL code in C#](https://stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp) — kͩeͣmͮpͥ ͩ, Jun 21 '18 at 09:36
Dupliate of https://stackoverflow.com/questions/589096/parsing-sql-code-in-c-sharp - which was closed as being off topic — kͩeͣmͮpͥ ͩ, Jun 21 '18 at 09:37
What does `Foo` do? Does it doe something easily doable in C#? Do you *absolutely need* it in the `WHERE` and `ORDER BY` statements? — Sefe, Jun 21 '18 at 09:40
It runs some decryption logic using SQL server built in column level encryption. — William1014, Jun 21 '18 at 09:53
Is that SQL in a stored procedure in the database or in strings in C# source code? — Andrew Morton, Jun 21 '18 at 09:54
I would suggest that you create a view in the database with the function call on the columns. — Gordon Linoff, Jun 21 '18 at 09:57
It sounds like you are storing the SQL statement as text in a table. You are then doing a "sp_executesql" to get the results. Is this a correct? — Jason Geiger, Jun 21 '18 at 15:40

Sefe · Accepted Answer · 2018-06-21T10:07:27.007

Instead of replacing each occurrence of every column, you can replace the statement...

FROM MyTable

...with a subselect that includes all existing columns with the function call:

FROM (
    SELECT dbo.Foo(ColumnA) AS ColumnA, dbo.Foo(ColumnB) AS ColumnB,
        dbo.Foo(ColumnC) AS ColumnC --etc.
    FROM MyTable
) AS MyTable

The rest of the query can remain unchanged. In case of table aliasing, you simply replace AS Table1 with AS t1.

Another option you should consider is to create views in your database that would be essentially the subselect. Combined with a naming convention, you can easily replace the occurrences in your FROM (and JOIN) statements with the view name:

FROM MyTable_Foo AS t1

If you want to replace all queries that you'll ever use, consider renaming the tables and creating views that are named like the old tables.

On a more general note: You should reconsider your approach to the underlying problem, since what you are doing here takes away much of the power of SQL. The worst thing here is that once you call the function on all columns, you will not be able to use the indices on those columns, which could mean a serious hit on DB performance.

Another advantage of this approach is that it doesn't change the output. In your example you had `ColumnE` and changed this to `dbo.Foo(ColumneE)` which means in the original output you would have had a column called `ColumnE` but in your amended script this column no longer has a name defined, so will appear as `Column`. — Richard Hansell, Jun 21 '18 at 10:01

SQL/C# - Apply a function to columns within a SQL query

1 Answers1