Delete from a table variable

Question

Post reply

Delete from a table variable

Kris Beicher

Old Hand

Points: 308
More actions
April 6, 2006 at 8:25 am

#365838

Hi,
I am trying to delete a few rows from a table variable, but I keep getting hit by 'Must declare the scalar variable "@tbl"' The table itself is working, I can insert data and I can display the table, just not delete from it. Any suggestions out there?
The bit of code I am using looks like this:
DECLARE @tbl TABLE (ID int, FirstName varchar(50))
...
DELETE FROM @tbl
WHERE @tbl.ID = (SELECT dbo.PersonList_Person.PersonID FROM dbo.PersonList_Person WHERE dbo.PersonList_Person.Include = 0)
Hope you can help me out here!
Best regards,
Kris

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

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

Jeet SSCommitted Points: 1536 More actions · Answer 1

if you drop the table name prefix in the where so it looks like...

DELETE FROM @tbl

WHERE ID = (SELECT dbo.PersonList_Person.PersonID FROM dbo.PersonList_Person WHERE dbo.PersonList_Person.Include = 0)

or

DELETE @tbl FROM @tbl as t

WHERE t.ID = (SELECT dbo.PersonList_Person.PersonID FROM dbo.PersonList_Person WHERE dbo.PersonList_Person.Include = 0)

Thanks Jeet

Kris Beicher Old Hand Points: 308 More actions · Answer 2

Thanks Jeet,

Tried the first one and it works!

Best regards,

Kris

arsum2 SSC Rookie Points: 41 More actions · Answer 3

Hi,

I have created a temp table (@TableName) with columns EmployeeId, EmployeeName and have inserted rows dynamically.

Now, I want to delete the those employees whose employeeIds are not in @EmpId

Below is my code for delete statement. I am getting "Invalid column name EmployeeId"

DECLARE @TableName varchar(300);

SET @TableName ='TEMPTABLE_' + CAST(@@SPID AS VARCHAR) + CONVERT(VARCHAR(255), NEWID());

SET @Query=' DELETE FROM '+ quotename(@TableName) +' WHERE '+ @EmpId +' NOT LIKE %'+cast(EmployeeId as varchar(3)) +'%';

execute(@Query)

Any help is appreciated

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Hang on...

Why dynamically generated temp table names? Why dynamic SQL? Looks like you're making your life difficult for no reason.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wschampheleer SSCertifiable Points: 5504 More actions · Answer 5

Gail certainly has a point there.

But to answer your question: SQL Server thinks EmployeeID is a column from a table, but you don't tell it which table it shoud come from.

Either you have forgotten to prefix with @ (in case EmployeeID is actually a variable) or you should turn the statment into a complete SELECT statement e.g.

SELECT @Query = ' DELETE FROM '+ quotename(@TableName) +' WHERE '+ @EmpId +' NOT LIKE %'+cast(EmployeeId as varchar(3)) +'%'

FROM some_table;

which will result in a useless statement if the query returns more than 1 record...

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

wschampheleer SSCertifiable Points: 5504 More actions · Answer 6

You may find this article on dynamic SQL and when (not) to use it interesting.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]