Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2006
    Posts
    5

    Unanswered: User Defined Variables

    I am running a procedure that declares a table with a user defined variable called ZipCodes8000. This datatype is on the systypes table as a valid datatype that we created. Here is the code and the error that is returned. Can anyone help me understand why I receive the error that I do.

    Code:

    DECLARE @SortPlanBinZipCodeRanges TABLE
    (
    SortPlanBinZipCodeRangeID INT IDENTITY (1, 1) NOT NULL,
    SortPlanBinID INT NULL,
    SortPlanID INT NULL,
    BinTypeID TINYINT NULL,
    BinFlagTypeID TINYINT NULL,
    BinNumber INT NULL,
    ZipCodeRanges ZipCodes8000 NULL
    )


    error:
    Msg 2715, Level 16, State 7, Line 1
    Column or parameter #7: Cannot find data type ZipCodes8000.

    Thank you in advance.

    Todd

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    user defined data types are not permitted in table variables
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    User-defined datatypes are at best useless, and at worst misleading. Don't bother with them.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by blindman
    User-defined datatypes are at best useless
    Well I like the idea of limiting your domain. For example:
    percentage_udd: DECIMAL(5,2)
    factor_udd: DECIMAL(3,2)

    Putting a constraint on them can even be more usefull
    percentage_udd: 0 <= @x <= 100
    factor_udd: 0 <= @x <= 1

    This way when you use percentage_udd as a datatype you look at the type and see it holds a percentage and you don't have to put constraints on every column with a percentage because the type already takes care of that.

    Sadly the implementation in SQL Server makes it really a nuisance to work with because:
    - You can't change the domain without dropping every last object that uses it
    - Scripting a table will transform the contraint to a column constraint
    - Putting a constraint on a column with a constraint on the user-defined-type is possible, they can be conflicting and you don't know which one will be enforced
    - Try changing the owner of a user-defined-type when it's accidently not owned by the right owner (should be dbo, but some devolper made a mistake which slipped through a few months back).

    All in all we don't use 'm anymore... so I partly agree with Blindman

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Lexiflex
    Well I like the idea of limiting your domain. For example:
    percentage_udd: DECIMAL(5,2)
    factor_udd: DECIMAL(3,2)

    Putting a constraint on them can even be more usefull
    percentage_udd: 0 <= @x <= 100
    factor_udd: 0 <= @x <= 1
    Niether of these benefits require the use of user-defined variables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by blindman
    Neither of these benefits require the use of user-defined variables.
    Nope but it makes it clear what kind of values you can expect and prevents you from forgetting to put a constraint on the column.

    It's vaguely the same as giving your variables and columns easy-to-understand names. Would you rather see:
    Code:
    DECLARE @x DECIMAL(18,6)
    or
    Code:
    DECLARE @discount percent_udd
    But again, we don't use 'm anymore so this a pure theoretical discussing

    Grtz, Lex

    PS. What am I doing here on a friday night!? I should be in a bar somewhere chatting up good looking girls... I'm off, have good weekend!

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I agree with blind one. My boss uses these because she says it enforces consistency across the database. I think it is just obfuscation.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Dec 2006
    Posts
    5
    Thank you all for you help on this issue

    Todd

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    CLR types in 2005, on the other hand, are another matter entirely. I haven't found a use for them yet though.

    Easy to confuse because in SSMS and elsewhere a CLR type is called "user defined data type" while what you are talking about is "user defined type".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •