USE [Database]

IF OBJECT_ID('[Procedures].[products_join_product_types]', 'P') IS NOT NULL
  DROP PROCEDURE [Procedures].[products_join_product_types];
GO

CREATE PROCEDURE [Procedures].[products_join_product_types]
AS
 SELECT [P].[id] AS [product_id], [P].[barcode],
        [P].[product_type_id] AS [product_type_id],
        [P].[name] AS [product_name],
        [PT].[name] AS [product_type_name]
 FROM [Tables].[products] AS [P]
 JOIN [Tables].[product_types] AS [PT]
 ON [PT].[id] = [P].[product_type_id]
GO