Temporary table variable 1

[datras]

Hi
We have tried to use Temporary table variable with a select statement in a stored procedure but not been able to make it work. Does anyone have a small example we can see, as the SQL help book is not providing any?

 

[bperry]

Here's a very small example.
Does that do it for you?

-- initially create the table variable
declare @sitetable table (SiteId char(4),SiteName char(10))

-- Insert some new records
INSERT into @sitetable
Select SiteId, SiteName From Sites

-- Now read the records from the table variable
Select COUNT(SiteID) as MyCount
From @sitetable Order by SiteId
--------------------------------
bperry

 

[datras]

Yes, that will work for a start!
Thanks

 

[datras]

Hi again bperry

What I am going for is more something like this:

CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)

as

declare @sqlstring nvarchar(500)
declare @tblIn table (Country char(4), Ship char(4), [LineNumber] int)

Set nocount on

set @sqlstring = N'INSERT into @tblIn SELECT Country, Ship, [LineNumber] FROM ' + @tbl


EXEC sp_executesql @sqlstring

However, when I run it in the SQL query analyzer I get the error that @tblIn need to be declared....

 

[bperry]

i am really crappy with this dynamic sql stuff (gotta learn more about it.)

But this same issue (i believe it's the exact problem) came up last week in another thread. Perhaps you'd like to look at it there: thread183-261787

 

[datras]

Well, that gave me this instead - a global temporay table:

set @sqlstring = N'SELECT Country, Ship, [LineNumber]INTO ##Temp FROM ' + @tbl

It doesn't seem to work with a local temp. tbl., so I must ensure that the table name change each time the procedure is called. However that is possible.

Thanks again!