passing database as parameter in stored procedure & switching database 2

[vbguy805]

Hi
I am trying to write stored procedure which use multiple database on same server but need to swich between databases..

create procedure dbo.ListImport(
@ps_ListDBName varchar(50),
@ps_OrderDBName varchar(50))
begin
Declare @iCount numeric
--use [@ps_ListDBName] how to do this?
select @iCount=count(*) from @ps_ListDBName..users
print @iCount
--use [@ps_OrderDBName] how to do this?
select @iCount=count(*) from @ps_OrderDBName..users
print @iCount
Select * from user--Output to text file..how to do this?
end


Any idea?

Thank you

 

[mrdenny]

You have to do the entire code against the remote database as dynamic SQL. You can't use a variable as the database name in a USE command.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbguy805]

its ok if I cant use "use" command but still I can do this right?

select @iCount=count(*) from @ps_ListDBName..users

How can i do this?

 

[mrdenny]

Nope, you can't use a variable as the object name either.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbguy805]

Thank you for your response..Well..I never done SQL-Server programming...but you think there is no way I can acheieve this huh?..even using embeded scripting..or using dll or something like that? Im just trying to be optimistic here

 

[SQLDenis]

You could do this, just run the whole thing in SQL 2000

use northwind
go

--sp_executesql 
DECLARE @ps_ListDBName VARCHAR(100), 
@intTableCount INT, 
@chvSQL NVARCHAR(100) 

SELECT @ps_ListDBName = 'pubs' 
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM  ' + @ps_ListDBName + '..authors'

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount  OUTPUT 

SELECT @intTableCount

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vbguy805]

Wow..It works!! Thank you very much

 

[mrdenny]

As I said above you have to do it all as Dynamic SQL which SQLDenis has shown.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbguy805]

Yea Thank you very much..Can you have DML in dynamic SQL?like Update/Insert statements returning rows affected? where i can find more information about dynamic sql?

 

[SQLDenis]

>>where i can find more information about dynamic sql?

Right here:-->

http://www.sommarskog.se/dynamic_sql.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

You can't really return data back from dynamic SQL to the calling code. The easiest way to do create a temp table in the calling code, run the dynamic sql, and within the dynamic SQL have it put the rows affected into the temp table. You can then query the temp table after the dynamic SQL has completed to get the rows affected.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbguy805]

I used @@rowcount after update/insert. you think its wrong idea to use @@rowcount?

 

[mrdenny]

Using @@ROWECOUNT from the calling code should be fine.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com