Troubl pointing quotes in string

[TysonLPrice]

I'm having trouble putting quotes into a quoted string. This is what I want to do inside an execute statement:

IF (object_id('tempdb..##tb1_uga') IS not Null)
DROP TABLE ##tb1_uga

But dynamically adding a suffix of user name to it.

What I have is:

declare @TableSuffix varchar(20)
set @TableSuffix = (select replace(system_user,'scmsna\','')) -- results in TPrice
declare @mytable varchar(30)
   select @mytable = '##tb1_uga' + @tableSuffix

execute ('''IF (object_id(''tempdb..''' + @mytable  + ''') IS not Null)
            DROP TABLE ##tb1_uga''' + @mytable + ''')

I thought ''' translated to a single quote but I keep getting sysntax errors running the execute.

The end result should be:

Execute('IF (object_id('tempdb..##tb1_ugaTPrice') IS not Null)  DROP TABLE ##tb1_ugaTprice')

 

[bborissov]

execute ('IF (object_id(''tempdb..''' + @mytable  + ''') IS not Null)
            DROP TABLE ' + @mytable + ')

NOT TESTED!



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Or better:

DECLARE @sql varchar(8000)
SET @sql = 'IF (object_id(''tempdb..''' + @mytable  + ''') IS not Null)
            DROP TABLE ' + @mytable

EXEC (@sql)

BTW I has typo in my previous answer:

execute ('IF (object_id(''tempdb..''' + @mytable  + ''') IS not Null)
            DROP TABLE ' + @mytable)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[TysonLPrice]

I think your example, you mentioned it wasn't tested, had an extra comma. This works:

execute ('IF (object_id(''tempdb..' + @mytable  + ''') IS not Null)  
          DROP TABLE ' + @mytable  )

Thanks for responding!

 

[simian336]

I am a fan of using char(39) in place of ' but not everyone likes that approach. It does make it eashier to understand what you are look at when you look at the code.

Simi

 

[JeffModen]

QUOTENAME works well for such things, as well.

My question would be, what's wrong with using a temp table so you don't have to do such existance checks?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[TysonLPrice]

My question would be, what's wrong with using a temp table so you don't have to do such existance checks?"

The code you see is just part of what I added into a clone of SP_HelpUser. It is run to capture all the DB roles into a global table that can be used in other sessions to do reporting and access in temporary Visual Basic programs. A global table is the only thing I am aware of that can be accessed across various sessions/software.

 

[JeffModen]

That would be true. Thanks for the feedback.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

You can simplify a bit by moving the parts out of dynamic sql that don't need to be there:

IF Object_ID('tempdb..' + @mytable) IS NOT NULL EXEC ('DROP TABLE ' + @mytable)

Just for fun, here's another way to handle it:

EXECUTE ('BEGIN TRY DROP TABLE ' + @mytable + ' END TRY BEGIN CATCH END CATCH')

Finally, looking at how you're doing this, if you set up each user with a different default schema, and then just use the same temp table name for each one but the correct schema for that user, it might solve the problem more neatly. Or perhaps use of CREATE SYNONYM could help somehow. If you can get away from using dynamic SQL and global temp tables, that would be great. Can you use real tables? How about a session-keyed (temp) table, despite its problems?

 

[TysonLPrice]

Thanks all! The project is to shift away from an application's internal security to DB roles. All this SQL will "go away" when the effort is complete.

 

[JeffModen]

All this SQL will "go away" when the effort is complete

Ummmm... If the application will still talk to the DB, I'd suggest that they actually do some proof of principle work performance wise on the heavier lifting before they make that commitment. Things like Hibernate and N-Hibernate are great for basic access but can cause some real performance problems on other things including high volumes of traffic across the "pipe".

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row