Posted by Steve on 01/10/07 16:04

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

• Next in forum: [OT}Re: Picture in database
• Prev in forum: Re: Import flat file into SQL Server 2005 Express
• Thread view: Re: giving a proc database and table names as parameters

[Reply to this message]