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
|
