Pass by reference 2

[hrhadin]

I am attempting to write code that drops tables/databases based on a date. I have several databases/tables with the following names:

Database - XXX_122006
tables - PT_122006
PD_122006

Database - XXX_012007
tables - PT_012007
PD_012007

I have the following code:

declare @ldProcDate smalldatetime
declare @lcProcDate char(10)
declare @lcDB char(17)

set @ldProcDate = '01-01-2007 00:00:00'
set @lcProcDate = convert(char, @ldProcDate, 103)
set @lcDB = 'XXX_' + substring(@lcProcDate, 4, 2) + substring(@lcProcDate, 7, 4) + '.Dbo.PT'

IF OBJECT_ID(@lcDB, 'U') is not null
drop table @lcDB

When I run this code I receive a general syntax error. In other languages one would place an '&' in front of the @lcDB to pass the variable by reference. How would this be accomplished in TSQL?

 

[gmmastros]

Dynamic SQL, like this..

[tt][blue]
Declare @SQL VarChar(8000)

IF OBJECT_ID(@lcDB, 'U') is not null
Begin
Set @SQL = 'drop table [' + @lcDB + ']'
Exec @SQL
End
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hrhadin]

Tried your code, received this error:

The name 'drop table [XXX_012007.Dbo.PT]' is not a valid identifier.

 

[gmmastros]

[tt][blue]
Declare @SQL VarChar(8000)

IF OBJECT_ID(@lcDB, 'U') is not null
Begin
Set @SQL = 'drop table [' + @lcDB + ']'
Exec [!]([/!]@SQL[!])[/!]
End
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hrhadin]

Thanks George - That's a big step in the right direction. However it's still not working. What's the difference between running the above code, which returns the error message:

Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'XXX_012007.Dbo.PT', because it does not exist or you do not have permission.

and:

drop table XXX_012007.Dbo.PT, which works?

Very frustrating!

 

[gmmastros]

OK. I finally see what the problem is.

When referencing objects (like databases, owners, tables and columns), it is safer to use square brackets around the object's name. By using the square brackets, you can use objects that have spaces in their names or are reserved words.

If you change the code to...

Declare @SQL VarChar(8000)

IF OBJECT_ID(@lcDB, 'U') is not null
  Begin
    Set @SQL = 'drop table ' + @lcDB
    Exec (@SQL)
  End

... I'm betting that it would work.

Think, for a minute, what waw getting executed by the statement when you left the square brackets in there.

Drop Table [XXX_012007.Dbo.PT]

This is not the correct way to do it. Instead, it should be...

Drop Table [!][[/!]XXX_012007[!]][/!].[!][[/!]Dbo[!]][/!].[!][[/!]PT[!]][/!]



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Nice, huh? I advised that you used square brackets, and then I 'left you hanging' with a solution that didn't use them.

Here's what I suggest you do...

declare @ldProcDate smalldatetime
declare @lcProcDate char(10)
declare @lcDB char([!]50[/!])
[!]Declare @SQL VarChar(8000)[/!]
    
set @ldProcDate = '01-01-2007 00:00:00'
set @lcProcDate = convert(char, @ldProcDate, 103)
set @lcDB = '[!][[/!]XXX_' + substring(@lcProcDate, 4, 2) + substring(@lcProcDate, 7, 4) + '[!]][/!].[!][[/!]Dbo[!]][/!].[!][[/!]PT[!]][/!]'

IF OBJECT_ID(@lcDB, 'U') is not null
[!]  Begin
    Set @SQL = 'drop table ' + @lcDB
    Exec (@SQL)
  End[/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[hrhadin]

Thanks for all your help George. It works!