From e76ab3a1ac2487e0dcb16ecdfe0c6e53c3f1e86c Mon Sep 17 00:00:00 2001
From: Boris Kolpackov <boris@codesynthesis.com>
Date: Fri, 29 Mar 2013 18:51:07 +0200
Subject: Add test for custom-mapping SQL Server SQL_VARIANT type

---
 mssql/custom/custom.sql | 42 ++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 42 insertions(+)
 create mode 100644 mssql/custom/custom.sql

(limited to 'mssql/custom/custom.sql')

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
-- 
cgit v1.1