Posted by Muhammed Al-Khoutani on 01/10/07 12:22

Hello!

Is it possible to pass a stored procedure a parameter, say @table and
use it as a table in the sql command?
Finally i want a proc to copy tables from a database to another database.

THERE IS MY CODE:

CREATE PROCEDURE [user].[copytable]
@dbSRC varchar(100),
@dbTRGT varchar(100),
@table varchar(100)
AS

BEGIN TRANSACTION FreeAndCopyTable
TRUNCATE TABLE [@dbTRGT].admin.[@table]
INSERT INTO [@dbTRGT].admin.[@table]
SELECT *
FROM [@dbSRC].admin.[@table]

COMMIT TRANSACTION FreeAndCopyTable
IF @@error <> 0 GOTO E_Copy_Fail

E_Copy_Fail:
ROLLBACK TRANSACTION FreeAndCopyTable

GO

thanks in advance,
muh

[Back to original message]