Results 1 to 2 of 2

Thread: SP Param sizes

  1. #1
    Join Date
    Feb 2002

    Unanswered: SP Param sizes

    I'm new to SQLserver and building SPs. Its driving me crazy having to hard code string param field sizes into the SP when they are going to relate to database column names (insert, select, etc).

    If my database changes (I change the column varchar size) I'll have to go change all my SPs? Doesn't sound like a fun day, week...

    Is there a better way?

    Can I set them huge, and then test for size within the sp? Can I get the db column size using t-sql?


  2. #2
    Join Date
    Jan 2002
    To get the column size you can use the following sql statement.
    LOAN is the table name and LOAN_TYPE_KY is the column name in that table

    JOIN SysTypes ON
    (SysTypes.XType = SysColumns.XType AND
    SysTypes.Name = 'VARCHAR')
    SysColumns.Id = OBJECT_ID('LOAN') AND
    SysColumns.Name = 'LOAN_TYPE_KY'

    As far as the database changes, one solutions might be to use user defined types in your tables and do the same in the SPs. That would take care of a situation where you have for example a user defined type MEDIUM_STRING VARCHAR(15) and then you decided to change it to VARCHAR(30).

Posting Permissions

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