IF Exists( ) in sql server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IF Exists( ) in sql server 2000

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-09 : 01:52:19

Hi

i want to check that Table Exis or not how it will done.In my stored procedure i pass Tablename by parameter.
my main problem is in EXISTS() i want to pass Tablename by variable.

IF EXISTS(select * from @user_id)begin

declare @s1 varchar(100)
set @s1='DROP table' +' '+ @user_id
exec(@s1)

Ranjeet Kumar Singh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-09 : 01:54:54

[code]If exists(select * from information_schema.tables where table_name = @tableName and Table_Type = 'BASE TABLE')
...[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-09 : 02:05:21

Thanks

How this task will done by Object_id() function i am using like this

if Object_id(@tablename) is not null
begin
declare @s1 varchar(100)
set @s1='DROP table' +' '+ @tablename
exec(@s1)
end

Ranjeet Kumar Singh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-09 : 02:17:26

That will work too. So what problem you are facing?

Note: From Object ID, you can't be sure whether it is table, stored proc, UDF, view etc.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2007-03-09 : 02:24:55

My procedure run sucessfully but does not return output

This is below

create procedure record
@user_id varchar(20)=null,@frtime varchar(100)=null,@totime varchar(100)=null
as
SET NOCOUNT on
declare @query varchar(2500),@exist varchar(100)
declare @tablename varchar(100),@yes varchar(10)
If exists(select * from information_schema.tables where table_name = @user_id and Table_Type = 'BASE TABLE')
begin
set @yes='yes'
set @tablename='DROP table' +' '+ @user_id
exec(@tablename)
select @query='select distinct tempaddplace.* into'+' '+ @user_id +' '+ ' from JOURNEY_REGT_DET
inner join assign_vehicle_access on
JOURNEY_REGT_DET.vehicle_id=assign_vehicle_access.vehicle_id inner join tempaddplace on
tempaddplace.Jrid=JOURNEY_REGT_DET.JOURNEY_ID where assign_vehicle_access.Assign ='''+@yes+''' and
assign_vehicle_access.user_id = ''' + @user_id + ''' and tempaddplace.Timerecorded between Convert(datetime,'''+@frtime+''',103) and Convert(datetime,'''+@totime+''',103)'
exec(@tablename)
end
else
begin
select @query='select distinct tempaddplace.* into'+' '+ @user_id +' '+ ' from JOURNEY_REGT_DET
inner join assign_vehicle_access on
JOURNEY_REGT_DET.vehicle_id=assign_vehicle_access.vehicle_id inner join tempaddplace on
tempaddplace.Jrid=JOURNEY_REGT_DET.JOURNEY_ID where assign_vehicle_access.Assign ='''+@yes+''' and
assign_vehicle_access.user_id = ''' + @user_id + ''' and tempaddplace.Timerecorded between Convert(datetime,'''+@frtime+''',103) and Convert(datetime,'''+@totime+''',103)'
exec(@tablename)
end
GO

-------------------------------
i execute this by this

exec record 'USR012','11-11-2006 00:00:00:00','20-11-2006 00:00:00:00'

But when i execute below line code this give error Invalid object name 'USR012'.select * from USR012

Ranjeet Kumar Singh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-09 : 02:43:18

Classical mistake!

You are building your query in @Query variable but executing @tablename variable.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources