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