Update in table valued function give syntax error unless the table variable is instantiated

• m4x

  SSC Enthusiast

  Points: 186

  More actions

  December 2, 2011 at 2:48 am

  #99501

  i was just playing around in sql and then found one strange thing... may be some one could explain me,

  create a function and update the table valued variable on a join condition.. but this one doesnt create,

  create FUNCTION fn_delete()

  RETURNS @t TABLE(DATA NVARCHAR(MAX),inte int)

  AS

  BEGIN

  DECLARE @table TABLE(DATA NVARCHAR(MAX),inte int)

  INSERT INTO @table SELECT 'one',1

  INSERT INTO @t SELECT 'one',1

  INSERT INTO @t SELECT 'two',2

  INSERT INTO @t SELECT 'three',3

  UPDATE @t SET DATA=t.DATA FROM (SELECT data,inte FROM @table)t INNER JOIN @table ON @table.inte=t.inte

  return

  END

  gives a syntax error

  Msg 137, Level 16, State 1, Procedure fn_delete, Line 12

  Must declare the scalar variable "@table".

  unless you change the line and give a alias name for the @table variable..

  UPDATE @t SET DATA=t.DATA FROM (SELECT data,inte FROM @table)t INNER JOIN @table t2 ON t2.inte=t.inte

  normally this update operation should be possible without any problem but any special restriction on table valued variables...?

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  December 2, 2011 at 3:19 am

  #1415776

  When referencing a table variable in a where / join predicate, you need to use an alias.

  -- Gianluca Sartori