aboutsummaryrefslogtreecommitdiff
path: root/mssql/custom/custom.sql
diff options
context:
space:
mode:
authorBoris Kolpackov <boris@codesynthesis.com>2013-03-29 18:51:07 +0200
committerBoris Kolpackov <boris@codesynthesis.com>2013-03-29 18:51:07 +0200
commite76ab3a1ac2487e0dcb16ecdfe0c6e53c3f1e86c (patch)
tree981c0f5581bdada9969d5b692dfacbc2c82f123c /mssql/custom/custom.sql
parent34d177d03f5020ca0ec4bf9b77e20951ed17ff29 (diff)
Add test for custom-mapping SQL Server SQL_VARIANT type
Diffstat (limited to 'mssql/custom/custom.sql')
-rw-r--r--mssql/custom/custom.sql42
1 files changed, 42 insertions, 0 deletions
diff --git a/mssql/custom/custom.sql b/mssql/custom/custom.sql
new file mode 100644
index 0000000..44ef512
--- /dev/null
+++ b/mssql/custom/custom.sql
@@ -0,0 +1,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