aboutsummaryrefslogtreecommitdiff
path: root/mssql/custom/custom.sql
blob: 44ef5126bbf768d9915f62346428df7f61dc3b3b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/* This file contains helper functions.
 */

IF OBJECT_ID('dbo.variant_to_string', 'FN') IS NOT NULL
  DROP FUNCTION dbo.variant_to_string;
GO

IF OBJECT_ID('dbo.string_to_variant', 'FN') IS NOT NULL
  DROP FUNCTION dbo.string_to_variant;
GO

CREATE FUNCTION dbo.variant_to_string (@val SQL_VARIANT) RETURNS VARCHAR(max)
AS
BEGIN
  RETURN CAST(SQL_VARIANT_PROPERTY(@val, 'BaseType') AS SYSNAME) + ' ' +
    CAST(@val AS VARCHAR(max))
END;
GO

CREATE FUNCTION dbo.string_to_variant (@val VARCHAR(max)) RETURNS SQL_VARIANT
AS
BEGIN
  DECLARE @ret SQL_VARIANT

  DECLARE @pos BIGINT
  DECLARE @vtype SYSNAME
  DECLARE @vtext VARCHAR(max)

  SET @pos = CHARINDEX(' ', @val)
  SET @vtype = SUBSTRING(@val, 1, @pos - 1)
  SET @vtext = SUBSTRING(@val, @pos + 1, LEN(@val))

       IF @vtype = 'tinyint'  SET @ret = CAST(@vtext AS TINYINT)
  ELSE IF @vtype = 'smallint' SET @ret = CAST(@vtext AS SMALLINT)
  ELSE IF @vtype = 'int'      SET @ret = CAST(@vtext AS INT)
  ELSE IF @vtype = 'bigint'   SET @ret = CAST(@vtext AS BIGINT)
  ELSE IF @vtype = 'char'     SET @ret = CAST(@vtext AS CHAR(8000))
  ELSE IF @vtype = 'varchar'  SET @ret = CAST(@vtext AS VARCHAR(8000))

  RETURN @ret
END;
GO