Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Question Unanswered: using variables in DDL

    Can I create a table using a variable for the table_name?? I need to run multiple versions of a stored proc simultaneously each if which creates tables to store data temporarily. So I need to generate a different table name for each version based on the input parameter for that version of the stored proc.
    Appreciate your help on this.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    If the data is only to be used in the stored procedure itself, or in the proc and procs called from it then you can use a temp table (#foo) which will localize the data to the current instance.

    The alternative is to create the table using an execute immediate call (note: requires ASE 12.0 or later):

    Code:
    declare @cmd varchar(200)
    select @cmd = 'create table ' + @name + '( col1 int, ....)'
    
    exec (@cmd)
    Michael

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    Thanks a lot for your response!
    I cannot use ' #tables ' as I have to retain the data even after the Stored proc has finished executing. The alternate solution works very well .
    Thanks again.

  4. #4
    Join Date
    Sep 2006
    Posts
    5

    related issue

    HI:
    Thanks to Michael, I ve been using the exec (@cmd) format with great success for create, 'select into ', insert stmts, within the stored proc. BUt now I've hit another challenge ....I need to read the rowcount of one of the tables into a variable...let me explain:

    - I create a table using this cmd:

    select @cmd = "select * into " + @name + " from table_name where 1=2 "
    exec (@cmd)
    - Similarly I insert rows.
    - Now I need to read the rowcount of the table into a variable. i.e.:
    select @cnt = count(*) from @name where col1 = @abc

    - I tried this:
    select @cmd = "select @cnt = count(*) from " + @name + "where col1 =" + @abc
    exec (@cmd)

    - but it gives the error: Must declare variable '@cnt'.
    even though I had declared it.
    Appreciate any help on this.

  5. #5
    Join Date
    Sep 2006
    Posts
    15
    You must declare the variable in your string:

    select @cmd = "declare @cnt int
    select @cnt = count(*) from " + @name + "where col1 =" + @abc

    If you intend to access this variable outside the execute immediate, you can save it into a temporary table to get it accross.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •