table data type in stored procedures

• AdoSun

  SSC Enthusiast

  Points: 177

  More actions

  October 8, 2003 at 2:36 am

  #106633

  Hello,

  Is there anyone who can tell me if there is a way to declare a table type variable in stored procedures?

  I’ve tried this:

  create procedure sp1

  (@table1 table (a1 int, a2 int))

  as

  select * from @table1

  go

  And I get the following error:

  Server: Msg 156, Level 15, State 1, Procedure sp1, Line 2

  Incorrect syntax near the keyword 'table'.

  Server: Msg 137, Level 15, State 1, Procedure sp1, Line 5

  Must declare the variable '@table1'.

  From Books OnLine:

  table variables can be used in functions, stored procedures, and batches.

  Edited by - adosun on 10/08/2003 02:41:49 AM

  Edited by - adosun on 10/08/2003 02:46:07 AM

• dan_jf

  SSC-Addicted

  Points: 496

  More actions

  October 8, 2003 at 3:08 am

  #477496

  They can be used in SP's but don't think they can be used as parameters.

  You have to delcare them within the body of the SP...

  DECLARE @table1 table (a1 int, a2 int)

  Dan