You can use ODBC Scalar Functions in Transact-SQL statements. These statements are interpreted by SQL Server. They can be used in stored procedures and user-defined functions. These include string, numeric, time, date, interval, and system functions.
Usage
syntaxsql
SELECT {fn <function_name> [ (<argument>,....n) ] }
Functions
The following tables list ODBC scalar functions that aren't duplicated in Transact-SQL.
String Functions
Numeric Function
Time, Date, and Interval Functions
Examples
A. Using an ODBC function in a stored procedure
The following example uses an ODBC function in a stored procedure:
SQL
CREATE PROCEDURE dbo.ODBCprocedure
(
@string_exp NVARCHAR(4000)
)
AS
SELECT {fn OCTET_LENGTH( @string_exp )};
B. Using an ODBC Function in a user-defined function
The following example uses an ODBC function in a user-defined function:
SQL
CREATE FUNCTION dbo.ODBCudf
(
@string_exp NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
DECLARE @len INT
SET @len = (SELECT {fn OCTET_LENGTH( @string_exp )})
RETURN(@len)
END ;
SELECT dbo.ODBCudf('Returns the length.');
--Returns 38
C. Using an ODBC functions in SELECT statements
The following SELECT statements use ODBC functions:
SQL
DECLARE @string_exp NVARCHAR(4000) = 'Returns the length.';
SELECT {fn BIT_LENGTH( @string_exp )};
-- Returns 304
SELECT {fn OCTET_LENGTH( @string_exp )};
-- Returns 38
SELECT {fn CONCAT( 'CONCAT ','returns a character string')};
-- Returns CONCAT returns a character string
SELECT {fn TRUNCATE( 100.123456, 4)};
-- Returns 100.123400
SELECT {fn CURRENT_DATE( )};
-- Returns 2007-04-20
SELECT {fn CURRENT_TIME(6)};
-- Returns 10:27:11.973000
DECLARE @date_exp NVARCHAR(30) = '2007-04-21 01:01:01.1234567';
SELECT {fn DAYNAME( @date_exp )};
-- Returns Saturday
SELECT {fn DAYOFMONTH( @date_exp )};
-- Returns 21
SELECT {fn DAYOFWEEK( @date_exp )};
-- Returns 7
SELECT {fn HOUR( @date_exp)};
-- Returns 1
SELECT {fn MINUTE( @date_exp )};
-- Returns 1
SELECT {fn SECOND( @date_exp )};
-- Returns 1
SELECT {fn MONTHNAME( @date_exp )};
-- Returns April
SELECT {fn QUARTER( @date_exp )};
-- Returns 2
SELECT {fn WEEK( @date_exp )};
-- Returns 16
Examples: Azure Synapse Analytics and Parallel Data Warehouse
D. Using an ODBC function in a stored procedure
The following example uses an ODBC function in a stored procedure:
SQL
CREATE PROCEDURE dbo.ODBCprocedure
(
@string_exp NVARCHAR(4000)
)
AS
SELECT {fn BIT_LENGTH( @string_exp )};
E. Using an ODBC Function in a user-defined function
The following example uses an ODBC function in a user-defined function:
SQL
CREATE FUNCTION dbo.ODBCudf
(
@string_exp NVARCHAR(4000)
)
RETURNS INT
AS
BEGIN
DECLARE @len INT
SET @len = (SELECT {fn BIT_LENGTH( @string_exp )})
RETURN(@len)
END ;
SELECT dbo.ODBCudf('Returns the length in bits.');
--Returns 432
F. Using an ODBC functions in SELECT statements
The following SELECT statements use ODBC functions:
SQL
DECLARE @string_exp NVARCHAR(4000) = 'Returns the length.';
SELECT {fn BIT_LENGTH( @string_exp )};
-- Returns 304
SELECT {fn CONCAT( 'CONCAT ','returns a character string')};
-- Returns CONCAT returns a character string
SELECT {fn CURRENT_DATE( )};
-- Returns today's date
SELECT {fn CURRENT_TIME(6)};
-- Returns the time
DECLARE @date_exp NVARCHAR(30) = '2007-04-21 01:01:01.1234567';
SELECT {fn DAYNAME( @date_exp )};
-- Returns Saturday
SELECT {fn DAYOFMONTH( @date_exp )};
-- Returns 21
SELECT {fn DAYOFWEEK( @date_exp )};
-- Returns 7
SELECT {fn HOUR( @date_exp)};
-- Returns 1
SELECT {fn MINUTE( @date_exp )};
-- Returns 1
SELECT {fn SECOND( @date_exp )};
-- Returns 1
SELECT {fn MONTHNAME( @date_exp )};
-- Returns April
SELECT {fn QUARTER( @date_exp )};
-- Returns 2
SELECT {fn WEEK( @date_exp )};
-- Returns 16
No comments:
Post a Comment
It’s all about friendly conversation here at small review :) I’d love to be hear your thoughts!
Be sure to check back again because I do make every effort to reply to your comments here.