Dynamic Sql Parse - Please help!

okay I have found the table name that I want dropped using the dynamic sql
below. I'm not quite sure how to populate @.TableToDrop for the actual drop
statement though. Can you help? I hope the indentation is not too bad and yo
u
can read the Statement ok
Set @.lnvchCommand = ' IF EXISTS ' +
' ( SELECT *
FROM WH_BKUPS..SYSOBJECTS
WHERE NAME like ' + '''' + @.lvchTable_Name + '_%' + '''' + '
AND NAME < ' + '''' + @.lvchTable_Name + '_' + @.exc_date_id + '''' +
' ) ' +
' Select @.lchTable_Exists = ''Y''' +
' Else ' +
Select @.lchTable_Exists = ''N'''
Execute sp_executesql @.lnvchCommand, @.ParmDefinition, @.lchTable_Exists OUTPU
T
If @.lchTable_Exists = 'Y'
Begin
DROP TABLE @.TableToDrop
EndUse dynamic SQL for the drop, for instance:
If @.lchTable_Exists = 'Y'
Begin
EXEC('DROP TABLE ' + @.TableToDrop)
End
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:4131048F-69B2-4765-88DB-E940E0E1B4AB@.microsoft.com...
> okay I have found the table name that I want dropped using the dynamic sql
> below. I'm not quite sure how to populate @.TableToDrop for the actual drop
> statement though. Can you help? I hope the indentation is not too bad and
you
> can read the Statement ok
> Set @.lnvchCommand = ' IF EXISTS ' +
> ' ( SELECT *
> FROM WH_BKUPS..SYSOBJECTS
> WHERE NAME like ' + '''' + @.lvchTable_Name + '_%' + '''' + '
> AND NAME < ' + '''' + @.lvchTable_Name + '_' + @.exc_date_id + '''' +
> ' ) ' +
> ' Select @.lchTable_Exists = ''Y''' +
> ' Else ' +
> Select @.lchTable_Exists = ''N'''
> Execute sp_executesql @.lnvchCommand, @.ParmDefinition, @.lchTable_Exists OUT
PUT
> If @.lchTable_Exists = 'Y'
> Begin
> DROP TABLE @.TableToDrop
> End
>|||You'll have to assign the table name to a variable and use EXEC or
sp_executesql for the DROP statement as well.
Why don't you know what tables exist in your database? This might be a
reasonable thing to do in an ad-hoc admin script (not really sure why
though) but in a business process application your table structures
ought to be static at runtime so this should never be necessary.
David Portas
SQL Server MVP
--|||thanks guys for your help. Much appreciated but I have found a solution.
what I would love how to do though is drop a table as follows
declare @.TableToDrop char(50)
select @.TableToDrop = 'marc'
print @.TableToDrop
drop table DBName..+@.TableToDrop
tearing my hair out...|||declare @.TableToDrop char(50)
select @.TableToDrop = 'marc'
print @.TableToDrop
EXEC('drop table DBName..' + @.TableToDrop)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:CF46E496-BD1C-47A0-9053-CFFADC337609@.microsoft.com...
> thanks guys for your help. Much appreciated but I have found a solution.
> what I would love how to do though is drop a table as follows
> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> drop table DBName..+@.TableToDrop
> tearing my hair out...|||> what I would love how to do though is drop a table as follows

> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> drop table DBName..+@.TableToDrop
This isn't supported because the basic concept for database
applications is that tables are created at design time and do not
change at runtime. In other words you should know what your table names
are and not need to parameterize them. That's why I asked why you need
to do this.
David Portas
SQL Server MVP
--|||Perhaps I should add that I fully agree with David, you should know the name
s of the tables and
there should be no need to, from an application, keep dropping tables. This
is most often a sign of
a mistake in the data modeling.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:eVD8i2aRFHA.2976@.TK2MSFTNGP10.phx.gbl...
> declare @.TableToDrop char(50)
> select @.TableToDrop = 'marc'
> print @.TableToDrop
> EXEC('drop table DBName..' + @.TableToDrop)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:CF46E496-BD1C-47A0-9053-CFFADC337609@.microsoft.com...
>|||This is not an RDBMS but a 1950's mag tape file system in a realllllly
bad disguise. The IBM convention for tape labels was "yyddd" , just
like you are using almost 60 years later!
SQL programmers have a schema that models the entities in their reality
in tables. Dropping and creating tables on the fly is an insanely poor
practice. It leads to dynamic SQL and other kludges. It says that
your model is in constant flux, never quite dependable. It says that
you live in a Phillip K. Dick world.
Get your boss to pay for some basic data modeling classes.