Post reply

passing table name as parameter to stored procedure

  • June 13, 2005 at 9:12 am

    #67541

    Hi,

    I have a question. I am trying to pass table name as parameter to the stored procedure. Inside the stored procedure, I am using the table name to insert into the table. I am getting errors while doing this...following is the code I am using...please let me know if I am missing any?

    CREATE PROCEDURE dbo.InsertPlusSales

    (

    @TableName VARCHAR(100),

    @MenuCatNo INT

    )

    AS

    TRUNCATE TABLE #tempCat

    INSERT INTO #tempCat

    SELECT sStore, SUM(Q)

    FROM dbo.Report5 R JOIN dbo.Stores S

    ON R.mStore = S.sStore

    WHERE iMenuCatNo = @MenuCatNo

    GROUP BY sStore

    DECLARE @sql VARCHAR(1000)

    SELECT @sql = 'INSERT INTO ' + @TableName

    SELECT @sql = @sql + 'SELECT A.Store, C.TotalQty/(A.TotalQty + B.TotalQty)

    FROM #tempEntrees A JOIN #tempKids B ON A.Store = B.Store

    JOIN #tempCat CON A.Store = C.Store'

    EXEC(@sql)

    Thanks,

    Sridhar!!

  • June 13, 2005 at 9:46 am

    #565040

    What errors are you getting exactly?

  • June 13, 2005 at 9:47 am

    #565043

    Looks like you might be running the tablename and the 'select' together.  Put a space in front ot the word 'select'.

    And then again, I might be wrong ...
    David Webb

  • June 13, 2005 at 9:47 am

    #565042

    My guess is that you are missing a space between @TableName and the following SELECT.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • June 13, 2005 at 9:49 am

    #565045

    Anybody wanna suggest another approach than dynamic sql to this problem??

    The Curse and Blessings of Dynamic SQL

  • June 13, 2005 at 10:13 am

    #565058

    Hi All,

    Thanks for the replies. It worked. Here is the situation why I am using Dynamic Sql. If there is another approach to this please please let me know. I would be glad to implement that approach...

    Basically I have a table in which I have columns named

    Period, Store, MenuCategory, MenuCategoryNumber, quantity and revenue.

    Now I have to create a report in .NET which will look like this

    Store FoodPlusSales BeveragePlusSales AppetizerPlusSales DessertsPlusSales

    10 10% 20% 15% 30%

    20 -5% 15% 6% 12%

    ....

    ....

    Now the Formulas for FoodPlusSales, BeveragePlusSales and so on are

    FoodPLusSales = SUM(Quantity of Foods)/(sum(Quantity of Entrees) + sum(Quantity of Kids))

    The MenuCategory would be different for Entrees, Kids and Foods...

    So I have created temp tables which will have Store Number and sum of Qty for each Category. Now I am joining these temp tables to insert into a table named FoodPlusSales. I need to do the same procedure for BeveragePlusSales, DessertsPlusSales. The only thing that changes is the table name and menu category. SO I decided to use Dynamic Sql to solve this.

    I hope you understood my problem. Please let me know if you have questions or if you have any workaround for this.

    Thanks,

    Sridhar.

  • June 13, 2005 at 10:53 am

    #565065

    Why are you using separate tables? Could this possibly be done by using just one table and an additional column in your primary key (identifying which table the record came from)?

    If the tables are identical but just have different names then this is the approach I would take.

  • June 13, 2005 at 11:28 am

    #565074

    Is this for reporting purposes, because if it's not, or if it's an asp report, this statement could easyly be done without any temp table nor dynamic sql??

  • June 13, 2005 at 11:32 am

    #565076

    Hi All,

    Thanks for the reply. This is for reporting in ASP.Net DataGrid. I am still not sure how to use just one table. Could you explain in some detail?

    Thanks,

    Sridhar.

  • June 13, 2005 at 11:41 am

    #565079

    Would be too hard with a datagrid (might be wrong here, didn't use 'em since I got my diploma).

    The idea would be to have a vertical select instead of a pivot table, then you could do the pivot on the client side (which would probabely takes much less time anyways).

    The query could look something like this (with much needed tweaking):

    SELECT Store, Category, min(TotalQty/(A.TotalQty + B.TotalQty))

    from dbo.YourTable(s)

    group by store, category

    This would give you all the data you need, but it would be on multiple rows. You'd have to work on the display client side. But the query would be a hell of a lot simpler (faster too).

  • June 14, 2005 at 9:54 am

    #565388

    Hi,

    Thanks for the reply and advise. we are using asp.net datagrid to display the results. And it seems that we cannot do that functionality in datagrid. That is why I have chosen this option. I know it is against all the rules to the data modelling. But right now I have only that solution. If you know how to do this please let me know.

    Thanks,

    Sridhar!!

  • June 14, 2005 at 10:10 am

    #565396

    I don't see any easy way out on this one... Maybe dynamic sql is the best solution here (never thaught I would say that).

    As it's already been said, your code fails because there's no space between insert into + @TableName and the next select 'Select'.

    Also keep in mind that the users will need direct access to the tables to be able to execute this proc.

Viewing 12 posts - 1 through 11 (of 11 total)

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