Function: [AdventureWorks2016CTP3].[dbo].[AUDIT_fn_SqlVariantToString]

CollapseAll image

Collapse image Function properties


 Name   Value 
 Schema   [dbo] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Type   Scalar 
 Is schema bound   
 Is deterministic   
 Encrypted   
 Implementation type   Transact SQL 
 ID   136387555 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Parameters


Name  Description DataType  Max length  ReadOnly
@var   sql_variant 8016  
@string   bit 1  

Collapse image Recordset returned


Name  Datatype  Max length 
@Return nvarchar 4000

Collapse image Objects that [dbo].[AUDIT_fn_SqlVariantToString] depends on


Object name Object type Dep level
[dbo].[AUDIT_fn_HexToStr] Function 1
Total 1 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

create function dbo.AUDIT_fn_SqlVariantToString(@var sql_variant, @string bit)
returns nvarchar(4000)
as
begin
declare
@type varchar(20),
@result nvarchar(4000)
set @type = cast(SQL_VARIANT_PROPERTY(@var,'BaseType') as varchar(20))
if (@type='binary' or @type='varbinary')
    set @result = cast(dbo.AUDIT_fn_HexToStr(cast(@var as varbinary(8000))) as nvarchar(4000))
else if (@type='float' or @type='real')
    set @result = convert(nvarchar(4000), @var, 3)
else if (@type='int'
    or @type='tinyint'
    or @type='smallint'
    or @type='bigint'
    or @type='bit'
    or @type='decimal'
    or @type='numeric'
    )
    set @result = convert(nvarchar(4000), @var)
else if (@type='timestamp')
    set @result = convert(nvarchar(4000), convert(bigint, @var))
else if (@string = 1)
    set @result = 'N''' + convert(nvarchar(4000), @var) + ''''
else
    set @result = convert(nvarchar(4000), @var)
return @result
end
GO

Collapse image See also


List of functions