Pasted

/*
 --------- Test queries for SQL_VARIANT_PROPERTY documentation ---------
 
Created By: Solomon Rutzky / Sql Quantum Leap ( https://SqlQuantumLeap.com/ )
Created On: 2020-02-25
Updated On: n/a
 
Location:               https://pastebin.com/edit/3RAFxLbH
Related PR:             https://github.com/MicrosoftDocs/sql-docs/pull/4218
Related Documentation:  https://docs.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql
 
*/
 
-- https://sqlquantumleap.com/2018/12/28/prevent-full-script-execution-understanding-and-using-parseonly-and-noexec/
SET PARSEONLY ON;
GO
 
 
 
-- Generate initial set of test queries (to copy and paste into this script).
-- Results of this query are the basis for the next section.
SELECT N'
DECLARE @Thing ' + [name] + N' = '
  + IIF([name] LIKE N'date%' OR [name] = N'time', 'GETDATE()', '1' ) + N';
SELECT ''' + [name] + N''' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, ''Scale'') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, ''Precision'') AS [Precision];
GO
'
FROM sys.types
WHERE  [precision] <> 0
OR [scale] <> 0
ORDER BY [name];
 
 
/* -- Condensed results from the next section. Items missing from documentation:
 
-- MISSING (p):
DATE -- 10
DATETIME2(s) -- if s = 0 then 19 else s + 20
DATETIMEOFFSET -- 34
DATETIMEOFFSET(s) -- if s = 0 then 26 else s + 27
DECIMAL / NUMERIC -- 18
TIME -- 16
TIME(s) -- if s = 0 then 8 else s + 9
 
 
-- MISSING (s):
DATETIME2(s) -- s (0 - 7)
DATETIMEOFFSET -- 7
DATETIMEOFFSET(s) -- s (0 - 7)
DECIMAL / NUMERIC -- 0
TIME -- 7
TIME(s) -- s (0 - 7)
*/
 
 
-- No need to list "float (s)" separately:
SELECT SQL_VARIANT_PROPERTY(CONVERT(FLOAT(4), 1), 'basetype');
-- real
 
 
-- Just in case anyone is curious why the "if s = 0" value is
-- always 1 less than for the other possible values (i.e. 1 - 7):
-- it's due to the decimal separator, which is missing when s = 0.
SELECT CONVERT(DATETIME2(1), SYSDATETIME()) AS [has decimal; 21 characters];
-- 2020-02-26 00:11:11.2
SELECT CONVERT(DATETIME2(0), SYSDATETIME()) AS [no decimal; 19 characters];
-- 2020-02-26 00:11:11
 
 
 
--------------------------------------------------------
 
-- The following tests were initially generated from the query shown
-- above. Then, tests for datatypes that are known to have optional
-- "scale" values (datetime2, datetimeoffset, float, and time) were
-- copied and pasted to create additional tests. No additional tests
-- were created for the decimal datatype as it was already documented
-- as having user-provided precision and scale.
 
 
DECLARE @Thing bigint = 1;
SELECT 'bigint' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing bit = 1;
SELECT 'bit' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing date = GETDATE();
SELECT 'date' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime = GETDATE();
SELECT 'datetime' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2 = GETDATE();
SELECT 'datetime2' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2(5) = GETDATE();
SELECT 'datetime2(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2(1) = GETDATE();
SELECT 'datetime2(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2(0) = GETDATE();
SELECT 'datetime2(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset = GETDATE();
SELECT 'datetimeoffset' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(5) = GETDATE();
SELECT 'datetimeoffset(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(1) = GETDATE();
SELECT 'datetimeoffset(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(0) = GETDATE();
SELECT 'datetimeoffset(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing decimal = 1;
SELECT 'decimal' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing float = 1;
SELECT 'float' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing float(25) = 1;
SELECT 'float(25)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing float(2) = 1;
SELECT 'float(2)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing int = 1;
SELECT 'int' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing money = 1;
SELECT 'money' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing numeric = 1;
SELECT 'numeric' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing real = 1;
SELECT 'real' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing smalldatetime = 1;
SELECT 'smalldatetime' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing smallint = 1;
SELECT 'smallint' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing smallmoney = 1;
SELECT 'smallmoney' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time = GETDATE();
SELECT 'time' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(5) = GETDATE();
SELECT 'time(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(1) = GETDATE();
SELECT 'time(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(0) = GETDATE();
SELECT 'time(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing tinyint = 1;
SELECT 'tinyint' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
--------------------------------------------------------
 
-- The BIT datatype is valid yet not mentioned in the "BaseType" section:
DECLARE @Table TABLE (c1 BIT)
INSERT INTO @Table (c1) VALUES (1);
SELECT SQL_VARIANT_PROPERTY(tmp.c1, 'BaseType') AS [Bit]
FROM   @Table tmp;
 
 
-- The following datatypes are not valid for SQL_VARIANT, hence
-- cannot be tested via SQL_VARIANT_PROPERTY():
DECLARE @NotForSqlVariant TABLE (c1 IMAGE, c2 TEXT, c3 NTEXT, c4 SQL_VARIANT, c5 ROWVERSION, c6 XML);
 
--------------------------------------------------------
 
 
 
GO
SET PARSEONLY OFF;
GO