Skip to content

Decimals

Jump to bottom
Jonathan Bairstow edited this page Sep 8, 2020 · 5 revisions

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

Reference data.

All procedures and functions described below generate values based on the values in [Reference].[Colour]. It is recommended that this table be populated with prerandomised data.

This table can be populated by executing the procedure [Reference].[usp_Populate_Colour]. Values can be added to, or removed from this table, but the following rules must be enforced:

  • Values for ColourSK must be continuous and have no gaps.
  • [SystemControl].[ReferenceControl] must be maintained so that the value for MaxReferenceSK accurately reflects the number of records in [Reference].[Colour].

The table can be repopluated, resolving any breaches of the above rules, by executing procedure [Reference].[usp_Maintain_Colour].

Procedures and functions

[SingleValue].[ufn_Get_Colour]

This function will return a random colour 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.

Example usages:

Declare	@Colour varchar(50)

Select	@Colour = [SingleValue].[ufn_Get_Colour]()

[SingleValue].[usp_Get_Colour]

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

Example usages:

Declare @Colour varchar(50)
exec	SingleValue.usp_Get_Colour @Colour out
Select	@Colour

[Fountain].[usp_Get_Colour]

This procedure will return the next available colour value. This method, whilst not strictly random, ensures that values are evenly over time. The generated value is returned via the output parameter @Colour, can be captured in a variable, or passed back to a calling application.

Example usages:

Declare @Colour varchar(50)
exec	Fountain.usp_Get_Colour @Colour out
Select  @Colour

[BulkValue].[usp_Get_Colour]

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

Parameter Data Type Default Value
@QuantityRequired smallint None

A recordset with the following structure is returned:

Field Name Data Type
Colour varchar(50)

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

Example Usage:

exec	[BulkValue].[usp_Get_Colour] 100

[BulkFountain].[usp_Get_Colour]

This procedure will return up to 10000 colour values. This method, whilst not strictly random, ensures that values are evenly over time. This procedure has the following input parameter:

Parameter Data Type Default Value
@QuantityRequired smallint None

A recordset with the following structure is returned:

Field Name Data Type
Colour varchar(50)

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

Example Usage:

exec BulkFountain.[usp_Get_Colour] 1000

Clone this wiki locally