Creating table in SP with parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating table in SP with parameter

Author

Topic

omicron777
Starting Member

9 Posts

Posted - 2008-11-15 : 13:29:49

I am trying to create a table with extracted columns from another database but having an undesired outcome. The parameter is supplied by the program. Please check the codes and guide me how to correct this.

= = =
CREATE PROCEDURE BM_sp_ME_getfiles
@tablename nvarchar(50)
AS
SELECT CM.idno, CM.cardno, CM.description_eng, CE.lastNcost, CE.lastDcost, CE.max_cost, CE.Avg_cost, CE.min_cost, CB.showbranch, CB.price AS PriceAll, CB.disp_qty AS ShowroomQty, CB.wh_stock AS WarehouseQty
into [monthend].[dbo].[@tablename]
FROM officesrv1.fg_imported.dbo.cardmaster CM INNER JOIN
officesrv1.fg_imported.dbo.CardMaster_Extension CE ON CM.cardno = CE.cardno INNER JOIN
officesrv1.fg_imported.dbo.Card_balances CB ON CM.cardno = CB.cardno
ORDER BY CM.cardno

GO
= = =

Instead of creating a table supplied by the program, it creates a table named @tablename.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-15 : 13:35:11

Why do you want dynamically create table everytime for same query? Can you explain your requirement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-15 : 13:36:50

you need to use dynamic sql for this

CREATE PROCEDURE BM_sp_ME_getfiles
@tablename nvarchar(50)
AS

DECLARE @Sql varchar(8000)
SET @Sql='SELECT CM.idno, CM.cardno, CM.description_eng, CE.lastNcost, CE.lastDcost, CE.max_cost, CE.Avg_cost, CE.min_cost, CB.showbranch, CB.price AS PriceAll, CB.disp_qty AS ShowroomQty, CB.wh_stock AS WarehouseQty
into [monthend].[dbo].[' + @tablename + ']
FROM officesrv1.fg_imported.dbo.cardmaster CM INNER JOIN
officesrv1.fg_imported.dbo.CardMaster_Extension CE ON CM.cardno = CE.cardno INNER JOIN
officesrv1.fg_imported.dbo.Card_balances CB ON CM.cardno = CB.cardno
ORDER BY CM.cardno'
EXEC(@Sql)

GO

omicron777
Starting Member

9 Posts

Posted - 2008-11-15 : 13:46:03

I need to extract the table every end of the month and at the same time serve as a backup copy of the main table. Every month I need to get the cost occurred during the previous month. The cost changes every few days, so I need to get the final cost and from there process a report needed by other departments.

Thanks for replying!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources