VistaDB 5
Calling CLR Functions using SQL Script

Calling CLR Methods using SQL

The following SQL code executes each of the CLR Functions and CLR Procedures setup in the previous steps. Calling these from SQL code is pretty straightforward.

Calling CLR Methods using SQL
Copy Code
-- Test Function 1 - ExportSchemaAndData - File will be called test.xml on disk -- Call the function and the return value will be returned from the select
SELECT ExportSchemaAndData( 'test2' );

-- Call the function without getting the return value can also be done like this -- ExportSchemaAndData( 'test2' ); -- Call the Function using a local variable for the result
DECLARE @exportok AS BIT;
SELECT @exportok = ExportSchemaAndData( 'test' );
IF ( @exportok = 1 )
    PRINT 'Data Exported OK';  -- See the LOG output pane for the PRINT output
ELSE
    RAISERROR( 'Data Export Failed', 1, 1 );

-- Test GetVersionFunction. It will return the database version using ado.net internally -- Will display the version in a column named VersionString
select GetVersionFunction() as VersionString;

-- THINGS NOT TO DO:-- EXEC on a function will not display anything!
EXEC GetVersionFunction();
-- Calling direct will not display anything!--GetVersionFunction(); -- Test Procedure version of GetVersion -- You have to declare the variable for the output and then exec the procedure -- then you can select the variable to display it
DECLARE @versionout AS NVARCHAR(4000);
EXEC GetVersionProcedure( @versionout );
SELECT @versionout as VersionString;
PRINT 'Version returned: ' + @versionout;

-- THINGS NOT TO DO: -- SELECT on a procedure will return the number of rows affected (0 in this case) -- this is usually NOT the desired behavior.
SELECT GetVersionProcedure(@versionout);
SELECT @versionout as VersionString; 
-- this still works, but you already returned a result set from the select above 

The -- lines above are comments and are meant to explain how the SQL code works. PRINT is a command to output the text to the LOG window of Data Builder.

See Also