Stored Procedures

Some people start their stored procedures with sp. That's a no no. SQL Server looks for "sp_" when looking for certain objects. To avoid name collisions, begin your stored procedures with "usp_".

Stored procedures are named according to their function in the pipeline.

Process Step	Naming Convention	Naming Model	Example
Pull Data	Name should start with the word pull and match the staging table the proc dumps to.	usp_Pull[DescriptionOfData]	usp_PullCustomerData
Clean Data	Name should start with the word clean and match the staging table the proc dumps to.	usp_Clean[DescriptionOfData]	usp_CleanCustomerData
Process MDM	Name should start with the word process and MDM and the dimension name it works on.	usp_ProcessMDM[DimensionName]	usp_ProcessMDMDimCustomer
Process Dimensions	Name should start with the word process and the dimension name it works on.	usp_Process[DimensionName]	usp_ProcessDimCustomer
Process Facts	Name should start with the word process and match the table name it works on.	usp_Process[FactTableName]	usp_ProcessFactCustomer
Finalize And Audit	Name should be descriptive.	usp_[DescribeProcess]	usp_MarkRecordsAsProcessed
Populate Reporting Tables	Name should start with the word load and end with the word table and should have the table name that it loads.	usp_Load[TableName]Table	usp_LoadCustomerReportTable
Monitoring	Name should describe the process.	usp_[DescribeProcess]	usp_DisplayTablesNotLoading