DROP TABLE @VariableName


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: DROP TABLE @VariableName

Hybrid View

  1. #1
    John C. Gunvaldson Guest

    DROP TABLE @VariableName

    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




  2. #2
    John C. Gunvaldson Guest

    Re: DROP TABLE @VariableName

    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




  3. #3
    DaveSatz Guest

    Re: DROP TABLE @VariableName

    @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
    >
    >
    >




  4. #4
    John C. Gunvaldson Guest

    Re: DROP TABLE @VariableName

    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
    > >
    > >
    > >

    >
    >




  5. #5
    DaveSatz Guest

    Re: DROP TABLE @VariableName

    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
    > > >
    > > >
    > > >

    > >
    > >

    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center