Post reply

Can a Store procudeure define an output table name?

  • July 15, 2005 at 5:14 am

    #167586

    Hi,

    I am using a stored procedure to create a table based on other tables in the database using a "Make Table" query.

    I want to use a parameter to define the output table name.

    It fails the syntax check when I define @TableName after the "INTO" clause.

    I have defined @TableName as a varchar(80)

    Does anyone know if this is possible?

    Regards

    Martin

     

     

  • July 15, 2005 at 5:31 am

    #574734

    Yes it's possible but it's also a sign of bad design... why do you need to create a new table on the fly like this?

  • July 15, 2005 at 5:35 am

    #574736

    It is a bit of a long story - but I need to create a number of result tables for comaprision.

    it is quicker to do on the server rather than creating  arecordset and then inserting into a new table.

    Any hints would be great

    Regards

     

    martin

     

  • July 15, 2005 at 6:34 am

    #574755

    If you want to compare results, I assume that you need to compare results for different queries on the same table so I'd just use query analyser for this and change the into tblname on each run. The proc in this case will just slow you down.

    Also if you need to compare performance (no reaccess the results) then I'd just do multiple select queries and see which one is fasted based on the profiler, i/o and execution plans.

  • July 15, 2005 at 6:39 am

    #574758

    Hi Remi,

    Thanks for your input.

    I am accessing the database from Visual basic 6. I need to generate perhaps up to 50 sets of results changing two paramters in the stored procedure.

    I was thinking of generating all the result tables and then I can work with them within my program.

    If there is a way to spaciy the output table name then that saves me some coding and makes things more effiecent. So if anyone know how I can specify the output table name through a parameter then that would be great.

    Regards

     

    martin

     

  • July 15, 2005 at 6:44 am

    #574763

    I'll show you how.. but DON'T DO THIS IN PRODUCTION DATABASE

    Declare @sql as varchar(8000)

    set @sql = 'Select col1, col2 into dbo.' + @tblName + ' from dbo.YourTable ...'

    exec (@Sql)

    The Curse and Blessings of Dynamic SQL

    Dynamic Search Conditions in T-SQL

    Arrays and Lists in SQL Server

  • July 15, 2005 at 7:52 am

    #574815

    Hi

     

    Thanks for your help. this worked fine.

    I accept what you say about the method and found the links interesting reading

    Regards

    Martin

  • July 15, 2005 at 7:56 am

    #574817

    HTH.

Viewing 8 posts - 1 through 8 (of 8 total)

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