giving a proc database and table names as parameters

[Muhammed Al-Khoutani]

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

[Steve]

Dynamic SQL:
Books Online: Using sp_executesql
Web: http://www.sommarskog.se/dynamic_sql.html

Muhammed Al-Khoutani wrote:

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

[Muhammed Al-Khoutani]

Thanks!
Everything is clear now :)

Steve schrieb:

Dynamic SQL:
Books Online: Using sp_executesql
Web: http://www.sommarskog.se/dynamic_sql.html

Muhammed Al-Khoutani wrote:

>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