Geek asks and answers

Can you create a URT CLR to allow a common table type for databases?

If I had an SQL statement such as:

CREATE TYPE [dbo].[typeRateLimitVariables] AS TABLE(
            [vchColumnName] [varchar](250) NULL,
            [decColumnValue] [decimal](25, 10) NULL
)

And I used it as a table variable for UDF in the database, I would have enough space. Let But say that I wanted to call scalar UDF from another database on the same server, then I would get an unknown type error.

I tried to create a type in the calling database, but obv. then I get a type mismatch, because although each of the UDTs has the same name, they have different areas and therefore different types.

I know that you can create CLR types, register the assembly on SQL Server and then gain universal access to that type.

My idea is to create a “TABLE” type CLR UDT, however I don’t see how this can be implemented, because I know that it should be a “SqlDbType.Structured” CLR type;

My questions:

  • Is there a way without using the CLR to create a global scope in SQL 2008 R2 for a table variable, and if not ...
  • How can I define a UDT in C # CLR in which a UDT is essentially an AS TABLE UDT
+3
source share
1 answer

I know that you can create CLR types, register the assembly on SQL Server, and then universal access to the user type.

? - , . "" - User-Defined Type . MSDN SQL Server:

UDT
UDT . , UDT, , . UDT , CREATE ASSEMBLY CREATE TYPE . , , , , , .

UDT , UDT . UDT :

  • , .
  • , .
  • UDT UDT UDT.

, , . Transact-SQL CAST CONVERT.

:

1) CLR SQL 2008 R2 , ...

, , CLR UDT, MSDN.

2) UDT # CLR, UDT UDT "AS TABLE"

, (.. "" " " ), (.. T-SQL UDF/Stored Proc vs SQLCLR UDF/Stored Proc).

EDIT:

( ) , USE, ad hoc/dynamic SQL, , , , :

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

USE [msdb];
GO

PRINT 'Creating [GlobalTableDef] Table Type in [msdb]...';
CREATE TYPE dbo.GlobalTableDef
AS TABLE
(
    [ID] INT NOT NULL IDENTITY(17, 22),
    [CreateDate] DATETIME NOT NULL DEFAULT (GETDATE()),
    [Something] NVARCHAR(2000) NULL
);
GO

PRINT 'Creating [TotalBytes] Function in [msdb]...';
GO
CREATE FUNCTION dbo.TotalBytes
(
    @TableToSummarize dbo.GlobalTableDef READONLY
)
RETURNS INT
AS
BEGIN
    DECLARE @TotalBytes INT = 0;

SELECT  @TotalBytes += (4 + 8 + DATALENGTH(COALESCE(tmp.Something, '')))
    FROM    @TableToSummarize tmp;

    RETURN @TotalBytes;
END;
GO

PRINT 'Testing the Table Type and Function...';
DECLARE @TmpTable dbo.GlobalTableDef;
INSERT INTO @TmpTable (Something) VALUES (N'this is a test');
INSERT INTO @TmpTable (Something) VALUES (NULL);
INSERT INTO @TmpTable (Something) VALUES (N'still seems to be a test');

SELECT * FROM @TmpTable;

SELECT dbo.TotalBytes(@TmpTable) AS [TotalBytesUsed];
GO

USE [tempdb];
GO
PRINT 'Creating [TypeTest] Proc in [tempdb]...';
GO

CREATE PROCEDURE dbo.TypeTest
AS
SET NOCOUNT ON;

    SELECT 1 AS [Step], DB_NAME() AS [CurrentDB];

    EXEC('
        SELECT 2 AS [Step], DB_NAME() AS [CurrentDB];
        USE [msdb];
        SELECT 3 AS [Step], DB_NAME() AS [CurrentDB];
        DECLARE @TmpTable dbo.GlobalTableDef;
        USE [tempdb];
        SELECT 4 AS [Step], DB_NAME() AS [CurrentDB];

        -- local query to prove context is tempdb
        SELECT TOP 5 * FROM sys.objects;

        INSERT INTO @TmpTable (Something) VALUES (N''this is a new test'');
        INSERT INTO @TmpTable (Something) VALUES (NULL);
        INSERT INTO @TmpTable (Something) VALUES (N''non-empty value'');
        INSERT INTO @TmpTable (Something) VALUES (NULL);
        INSERT INTO @TmpTable (Something) VALUES (N''woo-hoo!!!!!!!!!!!!!!!'');
        SELECT * FROM @TmpTable;

        SELECT [msdb].dbo.TotalBytes(@TmpTable) AS [TotalBytesUsed];
    ');

GO

USE [master];
GO

SELECT 5 AS [Step], DB_NAME() AS [CurrentDB];
EXEC tempdb.dbo.TypeTest;

--------------------------------

USE [tempdb];
GO
IF (OBJECT_ID(N'tempdb.dbo.TypeTest') IS NOT NULL)
BEGIN
    PRINT 'Dropping [TypeTest] Proc from [tempdb]...';
    DROP PROCEDURE dbo.TypeTest;
END;
GO

USE [msdb];
GO
IF (OBJECT_ID(N'dbo.TotalBytes') IS NOT NULL)
BEGIN
    PRINT 'Dropping [TotalBytes] Function from [msdb]...';
    DROP FUNCTION dbo.TotalBytes;
END;
GO

IF (EXISTS(
        SELECT  *
        FROM    sys.table_types stt
        WHERE   stt.name = N'GlobalTableDef'
    ))
BEGIN
    PRINT 'Dropping [GlobalTableDef] Table Type from [msdb]...';
    DROP TYPE dbo.GlobalTableDef;
END;
GO
+4

More articles:


All Articles