DROP TABLE @VariableName

[John C. Gunvaldson]

Q?

It seems T-Sql has some rules about which commands can pass
variables, and which cannot. DROP <anything> seems to not allow variable
passing... Say, inside a sp one wanted to use a _While_ loop to store a
variable name - and later use it, example:

-- While loop variables
DECLARE @lni INT
SET @lni = 0

-- Loop Through Import Tables and Drop Same.
WHILE @lni < 6 BEGIN

-- Increment variable for While Loop
SET @lni = ( @lni + 1 )

-- Which Table to Drop?
BEGIN SET @lcTable =
CASE WHEN @lni = 1 THEN 'Table_This'
WHEN @lni = 2 THEN 'Table_That'
WHEN @lni = 3 THEN 'Table_TheOtherThing'
END -- Case Statement

BEGIN
DROP TABLE @lcTable
END

etc...

What is the correct way to to use these commands, and pass a variable... I
have tried creating a string and executing it, like:

@SQL = "DROP TABLE " + @lcTable
EXEC &lcTable -- Cannot Find sp error returned....
EXEC sp_executesql &lcTable -- Error invalid column name, or
something equally as <not impressing me>


So, there must be another popular way?

tia


John C. Gunvaldson
San Diego, CA

[John C. Gunvaldson]

Ok, I am having a bad <typing> day :_(

I have tried EXEC @SQL and EXEC sp_executesql @SQL

- and did not get the reqd action, (I didn't use @lcTable... sigh)...

How to pass a variable to DROP TABLE @varname is still my Q?

tia

John

[DaveSatz]

@SQL = "DROP TABLE " + @lcTable
EXEC ( @SQL)


--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group)
-----------------------------------------------------------------

"John C. Gunvaldson" <foxnet_west@hotmail.com> wrote in message
news:39ee148e$1@news.devx.com...
> Q?
>
> It seems T-Sql has some rules about which commands can
pass
> variables, and which cannot. DROP <anything> seems to not allow variable
> passing... Say, inside a sp one wanted to use a _While_ loop to store a
> variable name - and later use it, example:
>
> -- While loop variables
> DECLARE @lni INT
> SET @lni = 0
>
> -- Loop Through Import Tables and Drop Same.
> WHILE @lni < 6 BEGIN
>
> -- Increment variable for While Loop
> SET @lni = ( @lni + 1 )
>
> -- Which Table to Drop?
> BEGIN SET @lcTable =
> CASE WHEN @lni = 1 THEN 'Table_This'
> WHEN @lni = 2 THEN 'Table_That'
> WHEN @lni = 3 THEN 'Table_TheOtherThing'
> END -- Case Statement
>
> BEGIN
> DROP TABLE @lcTable
> END
>
> etc...
>
> What is the correct way to to use these commands, and pass a variable... I
> have tried creating a string and executing it, like:
>
> @SQL = "DROP TABLE " + @lcTable
> EXEC &lcTable -- Cannot Find sp error returned....
> EXEC sp_executesql &lcTable -- Error invalid column name, or
> something equally as <not impressing me>
>
>
> So, there must be another popular way?
>
> tia
>
>
> John C. Gunvaldson
> San Diego, CA
>
>
>

[John C. Gunvaldson]

Thanks Dave,

You mean SET @SQL = -or- SELECT @SQL =

I believe all the folks are pointing me to useing the paranthesis '(' and
')' around the variable (I didn't before), Sounds good - and I very much
appreciate you jumping in.

Regards,

John Gunvaldson

"DaveSatz" <davidsatz@yahoo.com> wrote in message
news:39ef0070$1@news.devx.com...
> @SQL = "DROP TABLE " + @lcTable
> EXEC ( @SQL)
>
>
> --
> Thanks,
> David Satz
> Principal Software Engineer
> Hyperion Solutions
> ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
> (Please reply to group)
> -----------------------------------------------------------------
>
> "John C. Gunvaldson" <foxnet_west@hotmail.com> wrote in message
> news:39ee148e$1@news.devx.com...
> > Q?
> >
> > It seems T-Sql has some rules about which commands can
> pass
> > variables, and which cannot. DROP <anything> seems to not allow variable
> > passing... Say, inside a sp one wanted to use a _While_ loop to store a
> > variable name - and later use it, example:
> >
> > -- While loop variables
> > DECLARE @lni INT
> > SET @lni = 0
> >
> > -- Loop Through Import Tables and Drop Same.
> > WHILE @lni < 6 BEGIN
> >
> > -- Increment variable for While Loop
> > SET @lni = ( @lni + 1 )
> >
> > -- Which Table to Drop?
> > BEGIN SET @lcTable =
> > CASE WHEN @lni = 1 THEN 'Table_This'
> > WHEN @lni = 2 THEN 'Table_That'
> > WHEN @lni = 3 THEN 'Table_TheOtherThing'
> > END -- Case Statement
> >
> > BEGIN
> > DROP TABLE @lcTable
> > END
> >
> > etc...
> >
> > What is the correct way to to use these commands, and pass a variable...
I
> > have tried creating a string and executing it, like:
> >
> > @SQL = "DROP TABLE " + @lcTable
> > EXEC &lcTable -- Cannot Find sp error returned....
> > EXEC sp_executesql &lcTable -- Error invalid column name, or
> > something equally as <not impressing me>
> >
> >
> > So, there must be another popular way?
> >
> > tia
> >
> >
> > John C. Gunvaldson
> > San Diego, CA
> >
> >
> >
>
>

[DaveSatz]

correction:
SELECT @SQL = "DROP TABLE " + @lcTable
EXEC ( @SQL )

"John C. Gunvaldson" <foxnet_west@hotmail.com> wrote in message
news:39ef1a2d@news.devx.com...
> Thanks Dave,
>
> You mean SET @SQL = -or- SELECT @SQL =
>
> I believe all the folks are pointing me to useing the paranthesis '(' and
> ')' around the variable (I didn't before), Sounds good - and I very much
> appreciate you jumping in.
>
> Regards,
>
> John Gunvaldson
>
> "DaveSatz" <davidsatz@yahoo.com> wrote in message
> news:39ef0070$1@news.devx.com...
> > @SQL = "DROP TABLE " + @lcTable
> > EXEC ( @SQL)
> >
> >
> > --
> > Thanks,
> > David Satz
> > Principal Software Engineer
> > Hyperion Solutions
> > ->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
> > (Please reply to group)
> > -----------------------------------------------------------------
> >
> > "John C. Gunvaldson" <foxnet_west@hotmail.com> wrote in message
> > news:39ee148e$1@news.devx.com...
> > > Q?
> > >
> > > It seems T-Sql has some rules about which commands can
> > pass
> > > variables, and which cannot. DROP <anything> seems to not allow
variable
> > > passing... Say, inside a sp one wanted to use a _While_ loop to store
a
> > > variable name - and later use it, example:
> > >
> > > -- While loop variables
> > > DECLARE @lni INT
> > > SET @lni = 0
> > >
> > > -- Loop Through Import Tables and Drop Same.
> > > WHILE @lni < 6 BEGIN
> > >
> > > -- Increment variable for While Loop
> > > SET @lni = ( @lni + 1 )
> > >
> > > -- Which Table to Drop?
> > > BEGIN SET @lcTable =
> > > CASE WHEN @lni = 1 THEN 'Table_This'
> > > WHEN @lni = 2 THEN 'Table_That'
> > > WHEN @lni = 3 THEN 'Table_TheOtherThing'
> > > END -- Case Statement
> > >
> > > BEGIN
> > > DROP TABLE @lcTable
> > > END
> > >
> > > etc...
> > >
> > > What is the correct way to to use these commands, and pass a
variable...
> I
> > > have tried creating a string and executing it, like:
> > >
> > > @SQL = "DROP TABLE " + @lcTable
> > > EXEC &lcTable -- Cannot Find sp error returned....
> > > EXEC sp_executesql &lcTable -- Error invalid column name, or
> > > something equally as <not impressing me>
> > >
> > >
> > > So, there must be another popular way?
> > >
> > > tia
> > >
> > >
> > > John C. Gunvaldson
> > > San Diego, CA
> > >
> > >
> > >
> >
> >
>
>