How to refer User defined types in local table variable ?

• Hemant Kumar

  Ten Centuries

  Points: 1063

  More actions

  August 6, 2005 at 12:06 am

  #88595

  Hai ....,

            I have created a user defined type. It is of type varchar and size 50 not allowing null. When I try to use this data type for a local table variable as

  DECLARE @CUST_TABLE TABLE ( RecID INT IDENTITY ( 1, 1 ), CustCode CustomerCode )

  In the above declaration, CustomerCode is my user-defined data type. The error I encounter is as follows;

  Error 2715: Column or Parameter #2: Cannot find datatype CustomerCode

  How can I resolve this problem

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  August 6, 2005 at 2:38 am

  #580232

  Unfortunately, user-defined types cannot be used in table variables.

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• Hemant Kumar

  Ten Centuries

  Points: 1063

  More actions

  August 6, 2005 at 3:37 am

  #580235

  Ohhhh. OK thank you

• TimS

  Ten Centuries

  Points: 1165

  More actions

  August 6, 2005 at 7:47 pm

  #580259

  Hey it works in SQL2005; another reason to use it.

  CREATE TYPE dbo.CustomerCode4 FROM varchar(11) NOT NULL ;

  GO

  DECLARE @CUST_TABLE TABLE ( RecID INT IDENTITY ( 1, 1 ), CustCode dbo.CustomerCode4 )

  SELECT * FROM @CUST_TABLE

  GO

  Tim S