Post reply

Create Table from a variable

  • June 30, 2005 at 9:37 am

    #87941

    I have the following "start" to a new proc I'm going to be working on and am having an issue.

    Alter Procedure [ProcessCold]

    (  @Dept varchar(4)

      ,@Office varchar(5)

      ,@State varchar(2)

    )

    As

    DECLARE @SameCharity int,

            @DiffCharity int,

            @FileName varchar(20),

            @sql nvarchar(500)

    set @SameCharity=180

    set @DiffCharity=45

    set @FileName=left(@Dept,2) + @State + convert(varchar(2),datepart(Month,Getdate()))+'-'+convert(varchar(2),datepart(Day,getdate()))+'-'+@Office

    What I'd like to do is:

    CREATE TABLE @FILENAME (PHONE VARCHAR(10)

    But I'm receving an error when trying to do that because I'm assuming SQL doesn't allow you to directly create tables based off of variables?  Is there a work-around that I can use instead?

    Thanks.

  • June 30, 2005 at 9:39 am

    #569900

    DECLARE @FILENAME TABLE (PHONE VARCHAR(10))

    Regards,
    gova

  • June 30, 2005 at 9:42 am

    #569903

    Govin,

    I'm building the name of the file in the proc, therefor when I try the declare method you posted, I'm receving an error about a duplicated declaration.

  • June 30, 2005 at 9:44 am

    #569904

    That is because you already declared @FileName as VARCHAR(20). Change the name of any one of this you will be okay.

    Regards,
    gova

  • June 30, 2005 at 9:52 am

    #569910

    If you want this CREATE TABLE @FILENAME (PHONE VARCHAR(10)) you will need to build ALL of it into a variable and then EXEC the variable or sp_runsql....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 30, 2005 at 9:54 am

    #569913

    Can you elaborate on this, I'm sorry but I don't understand I guess.

    I'm wanting to send the proc the following:

    exec processcold 'ABCD','2,'TX'

    I'm wanting the proc to create a table named:

    ABTX6-30-2

    With the table name being programatically created with the set @filename statement. 

    What I'm unsure of though is how to integrate your declaration into the stored procedure and make things work.  Would you mind maybe re-writing your idea out for me so I get a clearer picture of what you're meaning?

  • June 30, 2005 at 9:57 am

    #569920

    NM, I figured it out with AJ's answer.

    Thanks much.

  • June 30, 2005 at 9:57 am

    #569919

    Why can't you use static sql for this task?

  • June 30, 2005 at 9:59 am

    #569922

    DECLARE @sql VARCHAR(2000)

    SELECT @sql = 'CREATE TABLE ' + @FileName + '(Phone VARCHAR(10) )'

    EXEC(@SQL)

    Thanks AJ Ahrens I did not take the question.

    Regards,
    gova

  • June 30, 2005 at 11:04 am

    #569996

    Another question along this same issue.

    I'm trying to create the insert statement and am running into problems because I use the - as part of my @FileName variable.  Anyone know the way around this other than simply changing the -?

    statement I'm using is:

    set @sql = 'Insert into ['+@FileName+'] select top 100 Phone,Title,Fname,Lname,Address,City,State,Zip,'+@Dept+' as Dept,'+'75'+','+@FileName+' as Batch from cold2'

    Note: the first @Filename is fine because it's the actual table, but I use the second @Filename to identify which "batch" of leads I'm sending out, and they just have always happened to be the same name.

  • June 30, 2005 at 11:17 am

    #570005

    Try this.

    Your missing the ' around the Character fiedlds your trying to insert

    When your having issues with dynamic sql,comment out the execute, and put a select statement prior to execution so you can see what the string looks like.

    set @sql = 'Insert into ['+@FileName+'] select top 100 Phone,Title,Fname,Lname,Address,City,State,Zip,'''+@Dept+''' as Dept,75,'''+@FileName+''' as Batch from cold2'

    Select @sql

    -- exec sp_ExecuteSql @sql

    Copy and paste the results from the select to a query analyzer window and your issues may become clearer.

    Please read, alot of information about dynamic sql, and why you may, or may not want to use.

    http://www.sommarskog.se/dynamic_sql.html

  • June 30, 2005 at 11:20 am

    #570008

    The real question is why do you want to many different tables that keep the same information?

  • June 30, 2005 at 2:11 pm

    #570138

    A good question indeed.

    A new table for each day of the week right?

    Thats about the only reason I can think of.

  • June 30, 2005 at 2:13 pm

    #570140

    Talk about crappy design... Seems to be on the topic a lot today.

  • June 30, 2005 at 2:27 pm

    #570150

    boy, slow down


    * Noel

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply