Sql-server – Migrating Oracle packages to SqlServer

Interesting mess. Multiple options if you're looking for model refactoring (recommended). If it's not the case/there are time constraints you can try to exploit EXEC. There's neat feature that is not very popular (maybe it's good). Core concept:

DECLARE @cmd NVARCHAR(4000)
SET @cmd = N'dbo.YourProcedure_v11'

DECLARE @retcode INT
EXEC @retcode = @cmd @Param1 = 1,
                     @Param2 = 2,
                     @ParamN = 'N'

You have to create stub procedure ('master' accessed from DAL) and call your procedures depending on user e.g.:

CREATE PROCEDURE dbo.YourProcedure
(
    @Param1 INT,
    @Param2 INT,
    @ParamN CHAR(1),
    @UserID INT
)   
AS
BEGIN

    SET NOCOUNT ON
    DECLARE @cmd NVARCHAR(4000)

    IF @UserID = 1
        SET @cmd = N'dbo.YourProcedure_v11'
    ELSE
        SET @cmd = N'dbo.YourProcedure_v12'

    DECLARE @retcode INT
    EXEC @retcode = @cmd @Param1 = @Param1,
                    @Param2 = @Param2,
                    @ParamN = @ParamN
    RETURN @retcode

END

Instead of passing UserID every time/to all procedures - you can use suser_sname etc depending on your security model. Also user-version mappings should be somewhere in database - stored procedure names are hardcoded only for testing.

This is a very good question and not just because it shows effort on your part to answer the question. Thank you for asking it and please do not remove it.

The answer as you have already discovered is -- You can't do that.

You have correctly identified several similar concepts including...

• Using standard types with constraints.
• Object Types that behave differently than standard types.
• Subtypes which are for PL/SQL not SQL.

Why might SQL Subtypes be useful? You mentioned consistency. Here are some ideas:

• Errors are prevented when inserting a field from one table into another.

• The definition of the field does not have to be rememberd or looked up.

• There is less typing.

• Changing the definition can be done in one place.

• The datatype name provides documentation.

Here are some thoughts addressing these:

• Table definitions are not changed frequently. Yes, they do change, but the amount of change is considerably smaller than that of PL/SQL (where you can use subtypes and %types) or application side code.

• Databases are usually normalized to eliminate duplication of data and limit the requirement for multiple tables to have fields with identical definition. Of course something as generic as a Money type would get used in many places, but some types would get moved to a lookup table and only the key repeated in multiple places.

• Often there are benefits to using distinct sizes for different tables. A discount store might have a products table with a field defined as Number(4,2) since they never sell anything over ten dollars. The definition is itself a constraint on the data a constraint that improves acuracy, documents the system, is fast, cannot be circumvented, and is all around better than any application side constraint. If the business decides to remove this constraint by increasing the field size, it does not necessarily mean it needs to change the size of other fields such as Salary, SalesAmount, etc.

• Denormalized data is often aggregated and may require different field sizes. A Salary column need not be Number(18,4) and even a sum of all salary columns company wide need not be Number(18,4) (just short of 100 trillion). Even if you need to increase the aggregated field containing Salary information due to growth of the company, you still wouldn't necessarily need to increase the Salary field at the same time, but if they are based on the same SQL subtype then you would have no choice. Right sized fields have the benefit of using less storage and memory as well as having faster index and table lookups.

• Remembering or looking up a custom type is similar in difficulty to remembering or looking up a previously used field definition.

• Oracle converts the SQL Server Money type to Number(19,4) when using the 10.2 Transparent Gateway, so even standardizing on Number(18,4) would be wrong when working with the gateway.

• In Oracle, frequently table columns are created that include definition that could be in the datatype name for other systems. For example, Wholesale with a datatype of Money might be WholesaleCost in Oracle so that it is clear the field holds relates to money. Sometimes this disabiguation would be necessary anyway such as if there were UnitCount, UnitVolume, and UnitCosts fields. Other times the name itself gives the type away (such as Salary). Finally, comments can be put on the table columns to provide further clarification if necessary.

I say all this not to say that the concept of an SQL subtype is bad, just that it is not as useful as it may seem.