Using "If exsists" statement to drop a table with a variable name

[Zukkster]

I'm creating a user specific table in my database. The table name will have the format

[dbo].[tablename_user1]

So the table will have variable names.

I'm trying to adapt the drop table statement (below) so that it will handle this variable table name.

if exists (select * from sysobjects where id = object_id(N'[dbo].[tblImportNCHRedemption]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblImportNCHRedemption]

I'm failing miserably.

I've tried seting two variables (@tablename) and (@SQL). I've been using these variables to write some code along the lines of

Set @SQL = 'If exsists etc. ' + @tablename + ' etc...
Execute(@SQL)

The problem is that the ' marks get all in a mix and messy

Please help

 

[sunila7]

You can use double quotes to delimit identifiers usiing

SET QUOTED_IDENTIFIER ON

Set @SQL = "If exsists etc. '" + @tablename + "' etc...
Execute(@SQL)

Have a look at this link for more info

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_8c32.asp

Sunil

 

[kkielak]

Are you runnig if exists in context of master database?

best regards
Chris

 

[Zukkster]

Sunila7,

That works. Fantastic, I've wasted hours on that.

I'm assuming I should

SET QUOTED_IDENTIFIER OFF

at the end of my stored procedure ... just so that I don't have problems later

Thanks

 

[sunila7]

Yes you should turn it off at the end of the SP, because the SET QUOTED_IDENTIFIER changes the way the object are delimited for example

will not work if it ON

Sunil