Using Table Type in SQL 2K giving error.

• Parag Barve

  SSC Rookie

  Points: 27

  More actions

  January 19, 2004 at 2:08 am

  #160705

  Trying to Use Table Data Type in Sql 2K, While I Use it directly in a Stored procedure, It works fine . But If the table declared is being populated using  a Dynamic SQL  The SP gives error while compiling itself.

  The Question is Are Table Data Types not usable in Dynamic SQL ?

  Here is the exmaple SP indiacting both the Sample Codes.

  --*********************

  Drop Procedure dbo.testTableType

  --******************

  Create Procedure dbo.testTableType

  as

  Declare @TestTable Table (testID nvarchar(25), TestDesc nvarchar(2000))

  Declare @TestSql nvarchar(2000)

  /* --- THIS IS THE PART WHICH DOES NOT COMPILE/WORK

  SELECT @TestSql = "INSERT INTO @TestTable SELECT au_id,au_fname From [pubs].[dbo].[authors] "

  EXECUTE (@TestSql)

  */

  /* --- THIS IS THE PART WHICH DOES COMPILE N WORK */

  INSERT INTO @TestTable SELECT au_id,au_fname From [pubs].[dbo].[authors]

  SELECT * FROM @TestTable

  return

  --******************

  EXECUTE testTableType

  --******************

  TIA

  Parag.

• Johan Bijnens

  SSC Guru

  Points: 135376

  More actions

  January 19, 2004 at 2:31 am

  #490618

  should be single quotes ..

   

  SELECT @TestSql = 'INSERT INTO @TestTable SELECT au_id,au_fname From [pubs].[dbo].[authors] '

  EXECUTE (@TestSql)

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 19, 2004 at 4:01 am

  #490628

  You might want to take a look at this comprehensive article on dynamic sql

  http://www.sommarskog.se/dynamic_sql.html

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• wz700

  SSCertifiable

  Points: 7133

  More actions

  January 19, 2004 at 3:21 pm

  #490738

  It is a cross session issue.

  Following code demo for that

  declare @sqlst varchar(1000)

  set @sqlst = 'declare @tbl table (au_id varchar(11));insert @tbl select au_id from authors;select * from @tbl'

  exec (@sqlst)