Passing local-table-variables to sp_executesql

• Aaron Orrell

  Mr or Mrs. 500

  Points: 510

  More actions

  February 9, 2007 at 9:31 am

  #116120

  Namaste ji,

  I am using bcp to output the result of a report-writing-procedure.  As far as I know, a persisted object is needed for bcp (scope-related) to function. 

  My task is to make the report-writing-procedure generic enough to generate reports based on table-driven parameters.  In order to have a basic procedure, the name of the report and it's identity are to be passed.  Consequently, dynamic sql will be used to build the report-result table.  I have information in a local TABLE variable the needs to inserted into the variable-report-result table.

  The main question is, "can a locally-declared-table-variable be passed to sp_executesql for dml?".  If so, does the table-variable need to be declared in the usual way in the @params argument? 

  Would it be better to use Execute in this scenario ?  Are local-table-variables visible to Execute?

  The following code is from part of the procedure and illustrates the desired functionality.  The bolded text is where the question is raised.

  --START

  DECLARE 
      @SQL NVARCHAR(4000),
      @SCHEMA SYSNAME, 
      @REPORTNAME SYSNAME, 
      @CPL INT
  IF EXISTS 
      (
          SELECT * 
          FROM dbo.sysobjects 
          WHERE 
              id = object_id(@SCHEMA + QUOTENAME('rpt' + @REPORTNAME)) AND
              OBJECTPROPERTY(id, N'IsUserTable') = 1
      ) 
      BEGIN
          SET @SQL = 'DROP TABLE ' + @SCHEMA + QUOTENAME('rpt' + @REPORTNAME) + ''
          PRINT @SQL
          EXECUTE SP_EXECUTESQL @SQL
      END
  SET @SQL = '
  CREATE TABLE ' + @SCHEMA + QUOTENAME('rpt' + @REPORTNAME) + '(
      [ID] INT IDENTITY (0,1) NOT NULL,
      [LINETEXT] VARCHAR(' + LTRIM(STR(@CPL)) + ') NOT NULL
  ) ON [PRIMARY]'
  PRINT @SQL
  EXECUTE SP_EXECUTESQL @SQL
  DECLARE @PAG TABLE
      (
          [ORDERBY] [INT] NOT NULL,
          [SECTIONKEY] [SMALLINT] NOT NULL ,
          [LINEKEY] [SMALLINT] NOT NULL ,
          [LINETEXT] [VARCHAR] (8000) NOT NULL 
      )
  SET @SQL = '
  INSERT INTO ' + @SCHEMA +  QUOTENAME('rpt' + @REPORTNAME) + '
      (LINETEXT)
      SELECT
          LINETEXT
      FROM
         @PAG
      ORDER BY
          ORDERBY,
          SECTIONKEY,
          LINEKEY
  '--SQL
  PRINT @SQL
  EXECUTE SP_EXECUTESQL @SQL, '@PAG TABLE', @PAG

  Should the @params read,

  '@PAG TABLE (

          [ORDERBY] [INT] NOT NULL,

          [SECTIONKEY] [SMALLINT] NOT NULL ,

          [LINEKEY] [SMALLINT] NOT NULL ,

          [LINETEXT] [VARCHAR] (8000) NOT NULL

      )'

  Kind regards,

  Aaron Orrell

• Aaron Orrell

  Mr or Mrs. 500

  Points: 510

  More actions

  February 9, 2007 at 9:53 am

  #688897

  Namaste ji,

  I just found this in the help:

  'Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.'

  If this turns out to be the case, then do you think I could use #PAG as a temp table table and would it be seen by the calling procedure without having to reference it explicitly?

  Kind regards,

  Aaron Orrell