selecting from a different db - problem

[adek]

Hi !

I have the following situation:
I have a table in which I specify a given DB. The table of the DB is known. I need to select info from that DB and table from inside a stored procedure. Follow? Like this:
select * from @OtherDB.dbo.Table where Col = @Parameter
The problem is that I get an error (Incorrect syntax near '.') when I have the statement like this. How do I correct the @OtherDB part so that it will work dynamically??

Tnx!!
//adek

 

[rt63]

Build a dynamic sql and execute it.


declare @sql varchar(200) , @parm_list varchar(100)
declare @parameter <data type of your parameter>
set @sql = 'select * from '+@OtherDB+'.dbo.Table where Col = @parm'
set @parm_list = '@parm <data type of your parameter>'

EXECUTE sp_executesql @sql, @parm_list,
@parm = @parameter


There is another way where you can execute the dynamic sql by building the entire sql as (assuming @parameter is integer)


declare @sql varchar(200)
set @sql = 'select * from '+@OtherDB+'.dbo.Table where Col = '+convert(varchar,@parameter)
execute (@sql)


In the first method you will not have to worry about converting the parameter datatype into string (remembering to get the right syntax). Moreover, first method will also set the @@rowcount variable if you need to know how many rows were affected by your sql.

RT