August 13, 2004 at 3:50 am
Hi,
If I have an SP defined under one DB. and I need to use this SP to work on another DB, is this posible?
I use the SP to write data in many different DB but with the same table definitions. The ide is that the SP only should be located one place. But I can't get this to work. I have tryed the USE keyword but no with result, and I have tryed to set the DB's name by parameter in the SP.
Thanks
Thomas Vanting
August 13, 2004 at 7:46 am
If I understand correctly, you'd need dynamic sql to make this work. If you are trying to minimize code and have the proc only run in one db, but reference a table in multiple ones, I think dnyamic SQL is the best way to go
August 16, 2004 at 12:24 am
Like Steve said, you'll need dynamic sql. Just create a stored proc with a parameter for the DBName and build your dynamic sql using that parameter.
If you need your dynamic procedure to return any values, you'll need to use sp_sqlexec instead of exec()
August 17, 2004 at 2:15 am
Hi, thanks for your answer.
If I understand the dynamic SQL correct, is it that you write the SQL code into an string and send this to the SQL server. If this is correct, then I have to manipulate the string when I need to use another database. This is an slow operation.
The database name send as parameter is not working, don't know why?
regards Thomas Vanting
August 17, 2004 at 3:10 am
Here's how you can NOT manipulate the string, but pass the dbname as a parameter to a stored proc. It's slower than a stored proc should be, but it's easier than creating bunches of stored procs.
create procedure sprTestDynamic
@DBName as varchar(128)
AS
Declare @strSQL as varchar(8000)
set @strSQL = 'Select * from ' + @DBName + '.dbo.tablename'
exec (@strSQL)
August 18, 2004 at 1:10 am
Hallo again,
I have tryed your exampel, but it's an large SP I use, so I havn't got it to work. But I'm trying.
An other thing,
Why can't I do this
DECLARE @TabelName varchar(100)
SET @TabelName = 'TestTabel'
SELECT * FROM @TabelName
Regards
Thoams Vanting
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply