Script to Define User-Defined Data Types

IF EXISTS (SELECT * FROM sysObjects
            WHERE id = object_id (N'usp_udt_define')
               AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE usp_udt_define
GO


CREATE PROC usp_udt_define
   @as_name       SysName,
   @as_DataType   VarChar (50),
   @as_null       VarChar (10) = 'NOT NULL',
   @ai_override   TinyInt = 0,
   @ai_debug      TinyInt = 0

AS
/*******************************************************************************
Arguments:  as_name      INPUT SysName = name of user-defined datatype (w/o prefix!)
            as_DataType  INPUT VarChar (50) = i.e. 'VarChar(20)'
            as_null      INPUT VarChar (10) = 'NULL' or 'NOT NULL'
            ai_override  INPUT TinyInt = When 0, _null is tacked onto name. In
                                       some cases, this is not desirable so pass
                                       in 1 and _null will not be added.
            ai_debug     INPUT TinyInt = When returning output to PB, any SELECT
                                       is FETCHed. So when run from PB, turn off
                                       the selects by setting this to 0.

Usage:      EXEC usp_udt_define 'id', 'Numeric (18,0)', 'NOT NULL'
            EXEC usp_udt_define 'id', 'Numeric (18,0)', 'NULL'
            -- Don't add _null by sending 1 to @ai_override
            EXEC usp_udt_define 'update_by', 'VarChar(50)', 'NULL', 1
            EXEC usp_udt_define 'update_dt', 'DateTime', 'NULL', 1

Description: Define specified user-defined datatype.

International Medical Group

Revision History:

Date       Project Author          Comments
01/09/2003 3173    Angie Kemerly   Created
*******************************************************************************/
DECLARE
   @ls_udtName       SysName,
   @ls_TableName     SysName,
   @ls_ColumnName    SysName,
   @ls_type          Char(1),
   @li_error         Integer,
   @li_ErrorNbr      Integer,
   @ls_ErrorMsg      VarChar (255),
   @ls_tab_col       VarChar (5000)

SET NOCOUNT ON

SELECT @ls_tab_col = '', @li_error = 0, @li_ErrorNbr = 0, @ls_ErrorMsg = ''

/* If making a null udt, tack on _null to @as_name. */IF @as_null = 'NULL' AND @ai_override = 0
   SET @ls_udtName = @as_name + '_null'
ELSE
   SET @ls_udtName = @as_name

/* 1. Unbind rules for udt */IF EXISTS (SELECT id FROM sysObjects
            WHERE id = object_id (N'rl_' + @as_name)
               AND ObjectProperty (id, N'IsRule') = 1)
   EXEC ('sp_UnbindRule ''udt_' + @ls_udtName + '''')

/* 2. Unbind default for udt (only for NOT NULL) */IF @as_null = 'NOT NULL' AND EXISTS (SELECT id FROM sysObjects
            WHERE id = object_id (N'df_' + @as_name)
               AND ObjectProperty (id, N'IsDefault') = 1)
   EXEC ('sp_UnbinDefault ''udt_' + @ls_udtName + '''')

/* 3. Rename type in order to modify it */IF EXISTS (SELECT name FROM sysTypes
            WHERE name = 'udt_' + @ls_udtName)
   EXEC ('sp_rename ''udt_' + @ls_udtName + ''', ''xudt_' + @ls_udtName + '''')

/* 4. (Re)Add type with modifications */EXEC ('sp_AddType ''udt_' + @ls_udtName + ''', ''' + @as_DataType + ''', ''' + @as_null + '''')
PRINT '[usp_udt_define]UserDataType added as ' + 'udt_' + @ls_udtName + ', ' + @as_DataType + ', ' + @as_null

/* 5. Bind rule */IF EXISTS (SELECT id FROM sysObjects
            WHERE id = object_id (N'rl_' + @as_name)
               AND ObjectProperty (id, N'IsRule') = 1)
   EXEC ('sp_BindRule ''rl_' + @as_name + ''', ''' + 'udt_' + @ls_udtName + '''')

/* 6. Bind default (only for NOT NULL) */IF @as_null = 'NOT NULL' AND EXISTS (SELECT id FROM sysObjects
            WHERE id = object_id (N'df_' + @as_name)
               AND ObjectProperty (id, N'IsDefault') = 1)
   EXEC ('sp_BinDefault ''df_' + @as_name + ''', ''' + 'udt_' + @ls_udtName + '''')

/* 7. (Re)Assign udt to columns *//* Get the columns that were temporarily assigned to a copy of the udt */DECLARE udt_cursor CURSOR FOR
   SELECT DISTINCT table_name, 
         /* This way the view will show up only once */         CASE xType WHEN 'U'
            THEN column_name
         ELSE NULL
         END, 
         xType
      FROM information_schema.column_domain_usage
         JOIN sysObjects
            ON table_name = name
      WHERE xType IN ('U', 'V')
         AND domain_name = 'xudt_' + @ls_udtName
IF @@Error <> 0 BEGIN
   SELECT @li_ErrorNbr = 50001, @ls_ErrorMsg = '[usp_udt_define]Declare of cursor failed.'
   GOTO Error
END

OPEN udt_cursor
IF @@Error <> 0 BEGIN
   SELECT @li_ErrorNbr = 50001, @ls_ErrorMsg = '[usp_udt_define]Open of cursor failed.'
   GOTO Error
END

FETCH NEXT FROM udt_cursor INTO @ls_TableName, @ls_ColumnName, @ls_type
WHILE (@@FETCH_STATUS <> -1) BEGIN
   /* @@FETCH_STATUS of -2 means that the row is missing. There is no need to
      test for this because this loop probably caused the row to 'disappear'. */   IF @ls_type = 'U' BEGIN
      /* Alter the table.column */      EXEC ('ALTER TABLE ' + @ls_TableName + 
         '   ALTER COLUMN ' + @ls_ColumnName + ' udt_' + @ls_udtName)
      SET @ls_tab_col = @ls_tab_col + @ls_TableName + '.' + @ls_ColumnName + ', '
   END
   ELSE BEGIN
      /* Refresh the view */      EXEC ('sp_RefreshView ' + @ls_TableName)
      SET @ls_tab_col = @ls_tab_col + @ls_TableName + ', '
   END
   FETCH NEXT FROM udt_cursor INTO @ls_TableName, @ls_ColumnName, @ls_type
END
SELECT @li_error = @@Error

DEALLOCATE udt_cursor
IF @li_error = 0
   /* No errors */   IF @ls_tab_col = ''
      PRINT '[usp_udt_define]No columns to reassign to udt_' + @ls_udtName
   ELSE
      PRINT '[usp_udt_define]Columns successfully reassigned to udt_' + @ls_udtName + ': ' + @ls_tab_col
ELSE BEGIN
   SELECT @li_ErrorNbr = 50001, @ls_ErrorMsg = '[usp_udt_define]Fetch of cursor failed.'
   GOTO Error
END
/* 7. End */
/* And finally drop temporary type */SET @ls_tab_col = ''

SELECT @ls_tab_col = @ls_tab_col + lTrim (rTrim (table_name)) + '.' +
   lTrim (rTrim (column_name)) + ', '
   FROM information_schema.column_domain_usage
   WHERE domain_name = 'xudt_' + @ls_udtName

IF @ls_tab_col <> ''
   PRINT '[usp_udt_define]UserDataType, x' + @ls_udtName + ', is still used: ' + @ls_tab_col
ELSE   /* Drop temporary udt that was renamed to xudt ... */   IF EXISTS (SELECT name FROM sysTypes
            WHERE name = 'xudt_' + @ls_udtName)
      EXEC ('sp_DropType ''xudt_' + @ls_udtName + '''')

/* If running from app that can show result set,
   this will display objects that are assigned to datatype. */IF @ai_debug = 1
   SELECT lTrim (rTrim (table_name)) + '.' + lTrim (rTrim (column_name))
      FROM information_schema.column_domain_usage
      WHERE domain_name = 'udt_' + @ls_udtName

SET NOCOUNT OFF

/* No errors */RETURN 0

/* Error Handler */Error:
   RAISERROR @li_error @ls_ErrorMsg
   RETURN 1

GO