SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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...

new SQL Server CLR project in Visual Studio
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

SQL Server CLR project using Visual Studio VB.NET Windows Class library template
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.

SQL CLR project solution explorer

Copy and paste following VB.NET source codes into Class1.vb replacing all existing codes

VB.NET codes for SQL CLR function for random integer generator
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.

Assembly file path for SQL Server CLR function
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.

SQL Server CLR function example code

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



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.