table variable query

hai · June 8th, 2007, 10:29 AM

I have a Stored procedure that use a temporary table. I am able to run the SP from query analyzer.
But if I run through Execute SQL Task Properties, I am getting error
"Invalid object name '#Codelist'"
I checked one topic here and I found that table variable will solve the issue.
I tried using the table variable but i am getting error.
Can someone say in detail how to use the table variable?
Can table variable be used in Stored procedure?

hai · June 8th, 2007, 12:23 PM

Can somebody help me out I tried creating a table variable but its throwing error as follows:
Must declare the variable '@CodeList'.
But I have declared the table variable in the top of code as
Declare @CodeList TABLE

The code I used is for update is
update @CodeList
    set enttype = (select a.description
            from @CodeList as c
                inner join dparty_anal as p
                    on p.code=c.Ccode
                inner join danal as a
                    on a.code = p.anal_code and a.sequence=p.sequence
            and @CodeList.Ccode = c.Ccode
            where p.sequence=24)

The error is happening at the following line
@CodeList.Ccode = c.Ccode
If I use the temporary table this logic works fine

joefawcett · June 8th, 2007, 01:02 PM

When you declare a table variable you need to define the structure of the table as you would when creating a normal table:

Code:

Declare @CodeList TABLE
(
  EntType NVARCHAR(100),
  OtherField INT
)

--

Joe (Microsoft MVP - XML)

hai · June 9th, 2007, 05:32 AM

Hi,
I have created the structure like what you have mentioned.. I havent mentioned it but I had already done it..

joefawcett · June 9th, 2007, 06:23 AM

The syntax for the update looks strange. Can you break it down so that you can test the inner query to see if it works as a standalone? The standard SQL Server syntax for updates is:

Code:

UPDATE @CodeList
  SET field1 = alias1.field2
    FROM @CodeList alias1 <JOIN> Table2 alias2 ON alias1.field2 = alias2.field2 [more joins]

--

Joe (Microsoft MVP - XML)

hai · June 11th, 2007, 05:19 AM

I have used the syntax because I have several update statement. In that one some are using aggregate functions. Aggregate functions cannot be assigned to a column.
e.g
     accdate = (select convert(char, max(ddate), 103)
        from @CodeListas s
            inner join ddate as r on
            r.code = s.Ccode
            and @CodeList.Ccode = s.Ccode
        where
            r.date_type = 'ACF')
It will be great if you can tell me how to modify the update statement with the same logic you mentioned earlier?