Decimals

This collection of SQL artefacts can be used to randomly generate decimal values, either singularly or in batches of up to 10000 values.

Reference data.

Bulk procedures described below generate values based on the values in [Reference].[Number]. This table can be populated by executing the procedure [Reference].[usp_Populate_Number].

Procedures and functions

[SingleValue].[ufn_Get_Decimal]

This function will return a random decimal value. As a function; this method can be used to easily assign a value to an variable, or to update a field on a table.

This function has the following input parameters:

Parameter	Data Type	Default Value
@MinDate	date	0.01
@MaxDate	date	99.99
@decimalPlaces	tinyint	2

Example usages:

Declare	@Number decimal(18,10)

Select	@Number = [SingleValue].[ufn_Get_Decimal]( 0.01, 99.99, 2)

[SingleValue].[usp_Get_Decimal]

This procedure will return a random decimal value. The generated value is returned via the output parameter @Number, and so can be captured in a variable or passed back to a calling application.

Parameter	Data Type	Default Value	Direction
@MinDate	date	0.01	Input
@MaxDate	date	99.99	Input
@decimalPlaces	tinyint	2	Input
@Number	decimal(18,10)	None	Output

Example usages:

Declare @Number decimal(18,10)
exec	[SingleValue].[usp_Get_Decimal] @MinValue = 95, @MaxValue = 100, @decimalPlaces = 2, @Number = @Number out
Select	@Number

[BulkValue].[usp_Get_Decimal]

This procedure will return up to 10000 random decimal values. This procedure has the following input parameter:

Parameter	Data Type	Default Value
@MinDate	date	0.01
@MaxDate	date	99.99
@decimalPlaces	tinyint	2
@QuantityRequired	smallint	100

A recordset with the following structure is returned:

Field Name	Data Type
Number	decimal(18,10)

This method is useful for inserting bulk data directly in to a table, or as an ETL source.

Example Usage:

exec [BulkValue].[usp_Get_Decimal] 0.01, 99.99, 2, 1000