The IN_DB_UTIL_SP_SCHEMA_CHECK_VERSION stored procedure will parse the current INUSER schema version and return schema specific attributes to populate variables with for conditional processing or for dynamic SQL generation.
If the CHECK_INOW_VERSION parameter is specified then the procedure will check if the current base schema version matches the provided schema version. If the CHECK_INOW_VERSION does not match the current base schema version then the procedure will return a value of one (1), otherwise it will return a value of zero (0).
The IN_DB_UTIL_SP_SCHEMA_CHECK_VERSION procedure will return the current INUSER base schema version (without the type), and the schema type (au, a, u), and the default character datatype (VARCHAR or NVARCHAR).
For example, if the current INUSER schema version is 7.7.0.0u then the procedure will return the following values:
@VERSION will be the base schema version of ‘7.7.0.0’.
@TYPE will be the schema type of ‘u’, signifying Unicode.
@DATATYPE will be the default character datatype for the schema of ‘NVARCHAR’ in this case.
@MESSAGE will contain an information message regarding the schema version check.
| Parameter | Description | |
|---|---|---|
| CHECK_INOW_VERSION | Type: | INPUT |
| Datatype: | NVARCHAR(40) | |
| Default Value: | IN_DB_UTIL_FN_GET_INOW_VERSION() | |
| Description | If specified, then the procedure will check if the current base
schema version matches the provided schema version. If
CHECK_INOW_VERSION does not match the current base schema version then
the procedure will return a value of 1 (error), otherwise it will return
a value of 0 (success). If no value is specified then the current schema version will be fetched using the function IN_DB_UTIL_FN_GET_INOW_VERSION() |
|
| VERSION | Type: | OUTPUT |
| Datatype: | NVARCHAR(40) | |
| Default Value: | NULL | |
| Description | The base schema version without the schema type designation. | |
| TYPE | Type: | OUTPUT |
| Datatype: | NVARCHAR(2) | |
| Default Value: | NULL | |
| Description | The default character datatype for the schema. If the schema encoding is Unicode (schema type includes ‘u’) then the datatype returned will be nvarchar, otherwise it will be varchar. | |
| Options |
Empty string for standard, ANSI schema type u = Unicode a = Advanced Filegroup Configuration au = Advanced Filegroup Configuration with Unicode |
|
| DATATYPE | Type: | OUTPUT |
| Datatype: | NVARCHAR(8) | |
| Default Value: | NULL | |
| Description | The default character datatype for the schema. If the schema encoding is Unicode (schema type includes ‘u’) then the datatype returned will be nvarchar, otherwise it will be varchar. . | |
| Options |
VARCHAR NVARCHAR |
|
| MESSAGE | Type: | OUTPUT |
| Datatype: | NVARCHAR(500) | |
| Default Value: | NULL | |
| Description | Contains an informational message regarding the results of the schema version check. | |
| V_RETURN_VAL | Type: | OUTPUT |
| Datatype: | INTEGER | |
| Default Value: | 0 | |
| Description |
Specifies whether the execution of the procedure was successful or not. 0 = If the CHECK_INOW_VERSION matches the current base schema version. (Success) 1 = If the CHECK_INOW_VERSION does not match the current base schema version then the procedure will return a value of one (1). (Error) |
|
EXAMPLE
DECLARE @V_SCHEMA_VERSION NVARCHAR(40), @V_SCHEMA_BASE NVARCHAR(40), @V_SCHEMA_TYPE NVARCHAR(2), @V_DATATYPE NVARCHAR(8), @V_INFO_MESSAGE NVARCHAR(500), @V_CALL_RETURN INTEGER; -- Get Current Schema Version SELECT @V_SCHEMA_VERSION = inuser.IN_GET_INOW_VERSION(); EXEC @V_CALL_RETURN = inuser.IN_SCHEMA_CHECK_VERSION @V_SCHEMA_VERSION, -- Or provide your own base version number to check for. @VERSION = @V_SCHEMA_BASE OUTPUT, @TYPE = @V_SCHEMA_TYPE OUTPUT, @DATATYPE = @V_DATATYPE OUTPUT, @MESSAGE = @V_INFO_MESSAGE OUTPUT; PRINT @V_CALL_RETURN; PRINT @V_SCHEMA_BASE; PRINT @V_SCHEMA_TYPE; PRINT @V_DATATYPE; PRINT @V_INFO_MESSAGE;