Post Areas

This collection of SQL artefacts can be used to randomly generate UK post area code and name 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].[PostArea]. It is recommended that this table be populated with prerandomised data.

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

• Values for PostAreaSK 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].[PostArea].

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

Procedures and functions

[SingleValue].[usp_Get_PostArea]

This procedure will return a random set of UK post area code and name values. The generated values are returned via the output parameters @PostArea and @PostAreaName, and can be captured in variables, or passed back to a calling application.

Example usages:

Declare @PostArea varchar(2),
	@PostAreaName varchar(50)
exec	[SingleValue].usp_Get_PostArea @PostArea out, @PostAreaName out
Select	@PostArea,
	@PostAreaName

[Fountain].[usp_Get_PostArea]

This procedure will return the next available set of UK post area code and name values. This method, whilst not strictly random, ensures that values are evenly allocated over time. The generated values are returned via the output parameters @PostArea and @PostAreaName, and can be captured in variables, or passed back to a calling application.

Example usages:

Declare @PostArea varchar(2),
	@PostAreaName varchar(50)
exec	Fountain.usp_Get_PostArea @PostArea out, @PostAreaName out
Select	@PostArea,
	@PostAreaName

[BulkValue].[usp_Get_PostArea]

This procedure will return up to 10000 random sets of UK post area code and name 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
PostArea	char(2)
PostAreaName	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_PostArea] 100

[BulkFountain].[usp_Get_PostArea]

This procedure will return up to 10000 sets of UK post area code and name values. This method, whilst not strictly random, ensures that values are allocated 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
PostArea	char(2)
PostAreaName	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_PostArea] 1000