Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Location
    Angola
    Posts
    12

    Unanswered: Creating a unique temp table.

    Set Quoted_Identifier On
    Go
    Set Ansi_Nulls On
    Go

    Alter Procedure spReport_SomeFooReport
    @SearchFromThisDate datetime = null, @SearchToThisDate datetime = null

    As
    Declare @TableUniqueIdentifier varchar(80), @SQLString varchar(5000)

    set @TableUniqueIdentifier = newid()
    set @TableUniqueIdentifier = 'Report_SomeFooReport' + @TableUniqueIdentifier
    set @TableUniqueIdentifier = replace(@TableUniqueIdentifier, '-', '7')
    set @SQLString = 'Create Table ' + @TableUniqueIdentifier + ' (xxx varchar(40))'
    exec @SQLString

    Return
    Go
    Set Quoted_Identifier Off
    Go
    Set Ansi_Nulls On
    Go


    -------------------------------------------
    the error is:
    Server: Msg 2812, Level 16, State 62, Line 12
    Could not find stored procedure 'Create Table Report_SomeFooReport06EEEC8D7EA6A74D0178EDD79E999B (xxx varchar(40))'.

    So may'be a format issue or something,
    im trying to create "temp" tables for sql 2005 report services in my Stored procedures which would have a sql job to get deleted at 23:00

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This looks like Sql Server. If that's the case, try using

    EXEC (@SQLString)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    RedNeckGeek is right, the EXEC without the parentheses means you're calling a stored procedure.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, it's bad form to create permanent tables on the fly from sprocs.

    If your database schema is well designed, there would be no need for this. Perhaps use a temp table instead?

    Or use a single permanent table for all your reports since they all have exactly the same structure, with just a single column, xxx varchar(40). You could add another column to identify the report instance.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would like to know howyou plan to reference that table in the future
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Brett Kaiser
    I would like to know howyou plan to reference that table in the future
    the only possible way I know of would be to return the table name as an out param from the sproc.

Posting Permissions

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