Monday, May 1, 2017

ODBC Scalar Functions in SQL Server

Do you know that you can call set of ODBC Scalar functions inside SQL Server? Yes, it is possible and they can be used with ad-hoc statements, stored procedures and functions.

There are different types of functions and they are categorized as String, Numeric, Date time, System Functions, and Data type conversion. You can see the list at https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-e-scalar-functions.

When you call an ODBC function, you need to make sure it is encased with curly brackets and started with fn. Arguments that are required for the function can be mentioned with parenthesis.

Here are some examples;

SELECT {fn ASCII ('A')} As Column1;
SELECT {fn Now()} As Column1;
SELECT 'Hello' + {fn Space(10)} + 'World' As Column1;
SELECT {fn USER()} As Column1;