CODEWITHSUNDEEP
sql-server-2005
user278618
user278618

Reputation: 20242

Problem with drop temp table

I have a script which works without 11 line

Declare c Cursor For Select Distinct InvestmentId From dbo.Investment
Open c
DECLARE @InvestmentIdTemp INT
Fetch next From c into @InvestmentIdTemp

While @@Fetch_Status=0 Begin
    Declare @tablevar table(col1 DATETIME)
    insert into @tablevar(col1) EXEC dbo.CalculateActualUpdateDate @InvestmentIdTemp
    UPDATE dbo.InvestmentData SET ActualUpdateDate =(SELECT TOP 1 col1 FROM @tablevar) WHERE InvestmentID=@InvestmentIdTemp

    DROP table @tablevar

   Fetch next From c into @InvestmentIdTemp
End

Close c
Deallocate c

where I have an error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@tablevar'.

How can I drop this table?

Upvotes: 0

Views: 255

Answers (1)

dknaack
dknaack

Reputation: 60486

as i say the table variable is no real table in the database, that means you cant "DROP" them. You can empty you table at the end of you while loop. You dont need to dispose the variable.

Declare c Cursor For Select Distinct InvestmentId From dbo.Investment
Open c

DECLARE @InvestmentIdTemp INT
Declare @tablevar table(col1 DATETIME)

Fetch next From c into @InvestmentIdTemp

While @@Fetch_Status=0 Begin
    insert into @tablevar(col1) EXEC dbo.CalculateActualUpdateDate @InvestmentIdTemp
    UPDATE dbo.InvestmentData SET ActualUpdateDate =(SELECT TOP 1 col1 FROM @tablevar) WHERE InvestmentID=@InvestmentIdTemp

    delete @tablevar

    Fetch next From c into @InvestmentIdTemp
End

Close c
Deallocate c

Upvotes: 2

Related Questions