Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121

    Unanswered: Just want to make sure (declare data type)

    I've reviewed the books on line looking to see if SQL Server 2005 would now include a feature that Oracle has for years. When you declare a variable in a procedure, function, etc you can declare them as a particular table and column type. Example:

    DECLARE
    @GHDEP2_SSNDEPENDENTID GHDEP2.SSNDEPENDENTID%TYPE;
    @GHDEP2_SSNDEPENDENTID1 GHDEP2.SSNDEPENDENTID%TYPE;

    ghdep2 is the table and ssndependentid is the column. This is great because I'll never run in to a truncation issue as the variable will always be whatever the table is. This may slow things down a bit doing that sort of check but I don't care.

    I just want to confirm that I can't do this in SQL 2005. All signs point to no.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. SQL Server does not have this functionality.
    Whether this feature is a good thing or a bad thing is debatable.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Thanks. I see it as a good think because I'll never have to go back and enlarge anything. I understand the overhead in validating these declarations but in this particular stored procedure that is OK.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    One downside is that you would have the freedom to change the schema on a whim...
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and your change, while cascading to database code, might very well break application code. Sprocs are supposed to be an insulating layer between the schema and the application.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Actually, in this case it would be the other way around. It is always changing the database for the application that breaks procedures on this end. Developers here have no idea what a dependency is I guess.

    Either way, I'm over it. Sometimes, it is a nice option to have.

Posting Permissions

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