How do I use EXISTS with a Dynamic Table Name in a STORED PROCEDURE

[aaqqwwss]

If my stored procedure takes in a table name, how do I verify that that table exists?

I have tried everything under the sun, like this:

incoming variable is called @TABLENAME='BLAH'


if exists
(@TABLENAME) --Gives an error

if exists
(Select * from @TABLENAME) --gives an error

if exists
(exec('select * from '+@tablename)) --gives an error



Please follow up with any suggestions.

 

[DLanouette]

Try

if exists (select * from dbo.sysobjects where id = object_id(@TableName) and OBJECTPROPERTY(id, N'IsUserTable') = 1)


______________________________
- David Lanouette
- Lanouette Consulting, LLC
- DLanouette@Computer.org

 

[PeaceMaker]

I am using this way... And 100% it works
I have to take dynamic names too cos i was using BCP from SP
and i need to access these simultaneously on multiuser environment
So here is the CODE

select @xLtt_Log = '##'+@zLaptop_Name+@zEmployee_id+'_Ltt_Log'

select @xCtr = 0
select @xCtr = count(*) from tempdb..sysobjects where name = @xLtt_Log

if Coalesce(@xCtr,0) > 0 Exec('Drop Table '+@xLtt_Log)
exec
('
create table '+@xLtt_Log+'
(
LogFld varchar(255)
)
')

Have Fun

PeaceMaker ( Ajay )
ajaydak@hotmail.com