0

We do create variables to store column data, like below

    DECLARE @Name VARCHAR(300)

    SELECT  @Name = Name,
            @Age = Age
    FROM [Employee]

Here, we give size as VARCHAR(300) in a assumption that length defined in table is 300.

And this happens across store procedures.

What if the size is increased in table ? (eg. 500). Is there a easy way to update the size across stored procedures ? or any better way of creating variables ?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Sreekumar P
  • 5,900
  • 11
  • 57
  • 82
  • How often do you change table structures to increase column widths? – Rigerta Jul 26 '17 at 06:33
  • Maybe in initial development phase we make many changes, that too if its a big distributed team, we never know someone made this change in DB – Sreekumar P Jul 26 '17 at 06:47

1 Answers1

1

I believe you want something like ORACLE %TYPE:

The Oracle %TYPE attribute adapts code as table definitions change. The %TYPE attribute provides the datatype of a variable or database column. If the column's type changes, your variable uses the correct type at run time. This provides data independence and reduces maintenance costs.

Microsoft SQL Server does not support and has no an equivalent of Oracle %TYPE.

SQL Server does not suppport this mechanism. Possible solutions:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    user-defined datatypes are initially appealing until you realise that you can't change the type definition (there is no `alter type`, and you can't even `drop` and `create` if the definition is actually in use elsewhere) – Damien_The_Unbeliever Jul 26 '17 at 07:07
  • @Damien_The_Unbeliever Yes, this is big drawback of user-defined types. – Lukasz Szozda Jul 26 '17 at 07:08