sql server object version control code example

Example: sql server version control

IF OBJECT_ID('dbo.p_DBVersion') IS NULL
  EXEC('CREATE PROCEDURE dbo.p_DBVersion AS SELECT 1')
GO

ALTER PROCEDURE dbo.p_DBVersion 
  @NEW_VERSION char(16) = NULL
AS
/*
    Nom:           p_DBVersion
    Autor:         Miquel Martí
    Creació:       18/03/19
    Propòsit:      Asignar o consultar versió de la BBDD
	Ús:	           EXEC p_DBVersion
	               EXEC p_DBVersion '1.1'
    
	Historial:     18/03/19 - Miquel Martí
                       + Creació inicial
*/
DECLARE @OLD_VERSION AS char(16)
SET NOCOUNT ON 

  -- consultar versió actual
  SELECT @OLD_VERSION = CAST([value] as char(16))
  FROM ::fn_listextendedproperty('Database Revision', default, default, default, default, default, default)

  -- si no existeix, asignar-li v0.0
  IF RTRIM(@OLD_VERSION) IS NULL 
    SET @OLD_VERSION = '0.0'

  -- si no s'especifica una nova versió, informar de l'actual
  IF RTRIM(@NEW_VERSION) IS NULL BEGIN
    SET @NEW_VERSION = @OLD_VERSION
    PRINT 'Database Revision is ' + 'v'  +  RTRIM(@NEW_VERSION)
  END
  ELSE BEGIN-- si s'especifica, informar-ne
    PRINT 'Upgraded Database Revision from ' + 'v' +  RTRIM(@OLD_VERSION) + ' to v' +  RTRIM(@NEW_VERSION)
  END

  -- actualitza Database Revision com a propietat avançada
  IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
    EXEC sp_dropextendedproperty N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
  EXEC sp_addextendedproperty N'Database Revision', @NEW_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
GO