Is there a way to output table variable from a dynamice SQL?

Question

Post reply

Is there a way to output table variable from a dynamice SQL?

Chris Lee-152270

SSCommitted

Points: 1809
More actions
April 13, 2007 at 10:30 am

#153526

Hi,
I have SQL 2000 and am trying to get result set into a table variable in a dynamic SQL statement. Process the table varibale outside dynamic SQL:
DECLARE @sql NVARCHAR(1000)
            ,@ParmDefinition NVARCHAR(500)
DECLARE @Multi TABLE (Attribute VARCHAR(100))
SET @sql = N'
INSERT INTO @Multi
   SELECT Attribute
   FROM   dbo.staging
   WHERE CHARINDEX(' + '''' + ',' + '''' + ', Attribute, 1) > 0'
         SET @ParmDefinition = N'@tMulti TABLE (AppAttribute VARCHAR(100)) OUTPUT'
         EXECUTE sp_executesql @sql, @ParmDefinition, @tMulti = @Multi OUTPUT
Somehow it doesn't recognize the @Multi table variable? I know there is a way to output other kind variables. Did I miss something? Thanks.
Chris

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

A table variable cannot be used as a parameter. (Also, it seems to be defined as @tMulti not @Multi.)

I see no point with dynamic SQL here. Why not just:

DECLARE @Multi TABLE

(

Attribute varchar(100) NOT NULL

)

INSERT INTO @Multi

SELECT Attribute

FROM dbo.staging

WHERE CHARINDEX(',', Attribute) > 0

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 2

Hi Ken,

Thanks for helping out. I actually simplied dynamic SQL a little bit:

SET @sql = N'

INSERT INTO @tMulti

SELECT Attribute (from 1 to 20)

FROM dbo.staging

WHERE CHARINDEX(' + '''' + ',' + '''' + ', Attribute, 1) > 0'

I had 20 attribute to check. So it should be something like:

...check which attribute column I am processing. Say it is fifth one:

DECLARE @sql NVARCHAR(1000)

,@ParmDefinition NVARCHAR(500)

DECLARE @Multi TABLE (AppAttribute VARCHAR(100))

SET @sql = N'

INSERT INTO @tMulti

SELECT' + Attribute5 +

N' FROM dbo.staging WHERE CHARINDEX(' + '''' + ',' + '''' + ', ' + Attribute5 + ', 1) > 0'

SET @ParmDefinition = N'@tMulti TABLE (AppAttribute VARCHAR(100)) OUTPUT'

EXECUTE sp_executesql @sql, @ParmDefinition, @tMulti = @Multi OUTPUT

@tMulti is a table variable inside dynamic SQL. @Multi is a table variable tries to reference inside one. So there is no way to output a table variable? That's really sad! Thankd.

Chris

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

If you need dynamic SQL, you can insert the results into a temp table. eg:

CREATE TABLE #Multi

(

Attribute varchar(100) NOT NULL

)

INSERT INTO #Multi

EXEC ('SELECT attribute FROM dbo.Staging WHERE CHARINDEX('','', attribute) > 0')

Chris Lee-152270 SSCommitted Points: 1809 More actions · Answer 4

Again thanks, Kan. If there is no way output the table variable. A temp table is only solution I have.

Chris

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Table function is probably another solution for you.

_____________
Code for TallyGenerator