How to paramaterized database in store procedure

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to paramaterized database in store procedure

Author

Topic

Diko
Starting Member

1 Post

Posted - 2002-01-08 : 09:07:18

I want to make a store procedure which the database as arguments, example in my case :

i want to transfer data from table X at database y to table X at database z. I have try with program below :

CREATE PROCEDURE [transfer]
(@DBSource [varchar](10),
@DBDestinatinon [varchar](10))

insert @DBDestination..TFULLNAME
select * from @DBSource..TFULLNAME

...

it's still get error syntax. Can u help me ..please

Nazim
A custom title

1408 Posts

Posted - 2002-01-08 : 09:29:58

you need to use dynamic Sql

somethign like this should help you.

CREATE PROCEDURE [transfer]
(@DBSource [varchar](10),
@DBDestinatinon [varchar](10))

declare @strstmt as varchar(300)

select @strstmt="insert into "+@dbdestination + "..tablename" + " select * from "+ @dbsource+"..tablename"
exec strstmt

Read this Article by Merkin

http://www.sqlteam.com/item.asp?ItemID=4599

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/08/2002 09:34:54

shankarc
Starting Member

37 Posts

Posted - 2002-01-08 : 09:30:46

You have to use dynamicSQL.

declare @Qry nvarchar(200)
Select @Qry = 'insert ' + @DBDestination + '..TFULLNAME '
+'select * from ' + @DBSource + '..TFULLNAME '

sp_ExecuteSQL @Qry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources