Generate Random Integer Number using SQL Server CLR Function
SQL Server developers can use SQL CLR function to create random number as random generation. This SQL CLR tutorial shows how to create a CLR project using Visual Studio. SQL tutorial continues with assembly registration and user defined function creation.
Launch Visual Studio 2015 or your installed Visual Studio IDE tool.
Create a new project using wizard by following menu options: File > New > Project...
Create new project in Visual Studio for SQL CLR Class Library
From Installed Templates, drill down for Visual Basic > Windows > Class Library
For this SQL CLR example which creates an assembly executed in SQL Server custom user function, I choose class library project with VB.NET. You can either choose C# if it is easier for you. In this tutorial, developers will find codes for VB.NET
Choose project template as Windows Class Library for SQL Server CLR functions
When you first create the CLR class library project, following structure can be viewed for CLR project in Visual Studio Solution Explorer window.
Copy and paste following VB.NET source codes into Class1.vb replacing all existing codes
Create random number using SQL CLR function
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Public Class KodyazSQLCLRFunctions
<SqlFunction()>
Public Shared Function RandomInteger(ByVal MinValue As SqlInt32, ByVal MaxValue As SqlInt32) As SqlInt32
Dim rnd As New Random()
Return rnd.Next(MinValue, MaxValue)
End Function
End Class
Compile the project using Visual Studio menu options Build > Rebuild Solution or using key combination Ctrl+Shift+B if you are building the class library solution for the first time.
Please note that it is best practice to build the latest project code in release mode instead of deploying the final solution to production in debug mode.
In Output window, build results are displayed as seen in below screenshot.
1>------ Rebuild All started: Project: KodyazRandomInteger, Configuration: Debug Any CPU ------
1> KodyazRandomInteger -> C:\DevProjects\VS Projects\KodyazRandomInteger\KodyazRandomInteger\bin\Debug\KodyazRandomInteger.dll
========== Rebuild All: 1 succeeded, 0 failed, 0 skipped ==========
Go to path where dll file is located. Copy the dll file and paste it into a secure folder where you have permissions to read from and safe to keep the dll file for backup.
Now launch SQL Server Management Studio and switch to database which you want to create your SQL CLR random function.
CREATE ASSEMBLY KodyazSQLCLRFunctions FROM
'C:\DevProjects\SQL\clr-library-folder\KodyazRandomInteger.dll'
WITH PERMISSION_SET = SAFE
If the Create Assembly command is executed successfully, then we can continue with SQL function creation using CLR class as our new assembly registered to target SQL Server database
Here is the SQL codes to create random SQL CLR function
CREATE FUNCTION SQLRandomIntegerCLRFunction (
@MinValue int,
@MaxValue int
)
RETURNS int
AS EXTERNAL NAME
KodyazSQLCLRFunctions.[KodyazRandomInteger.KodyazSQLCLRFunctions].RandomInteger;
--AssemblyName which you used during registration to sys.assemblies
--AssemblyName.[RootNamespace.ClassName].SQLFunctionName
GO
And here is how SQL developers can use this random integer generator SQL CLR function in their SQL codes
select dbo.SQLRandomIntegerCLRFunction(1, 100)
go
declare @random int
select @random = dbo.SQLRandomIntegerCLRFunction(1, 100)
select @random
Here is how SQL developers can call SQL Server CLR function for random integer generation.
For additional SQL tutorials on creating SQL Server CLR functions, Transact-SQL developers can review following tutorials on splitting string expressions:
SQL Server String Split T-SQL CLR Function Sample
SQL Server CLR Split String Function for 2-Dimensional Array