Technical Article

Script to Define User-Defined Data Types

,

Our DBA and I (I'm a PowerBuilder programmer) decided to plunge head-first into UDTs. After reading the message boards, I thought maybe we could come up with a way to make them work. My solution is this stored proc. There are some assumptions made that work for our needs and some extra work that was required to follow our database design (like the override), but overall it has worked well for us so far. We have only applied it to new tables so I can't vouch for the performance if you were to change a UDT that has been bound to hundreds of columns. I apologize for the horrible wrapping below, but if you copy it to Query Analyzer it will look much better. I'd love any suggestions on how to improve it. Thanks! - Angie

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating