joining with db where dbname is stored in field

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 2008 Forums

Transact-SQL (2008)

joining with db where dbname is stored in field

Author

Topic

MCPietersTP
Starting Member

15 Posts

Posted - 2013-05-21 : 05:53:19

Hi,
Here is the query which tell's you what i want to do:
DECLARE @DBName nvarchar(100)
declare @sql nvarchar(max)

begin
set @sql = 'select au_name, Number_of_Rows from (select ' + @DBName + '=au_SQLDatabaseName ,au_name from dbo.tbl_Audits WHERE (au_Acquisition = 1) AND (au_Production = 1))
cross apply (SELECT count(*) as Number_of_Rows from [' + @DBName + '].dbo.tbl_financialdata ) as counts ';
execute(@sql);
end

how can i make this query working?

MCPietersTP
Starting Member

15 Posts

Posted - 2013-05-21 : 05:57:08

Because now the only message i get is: Commands completed successfully
and no results

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 05:58:02

you need to use sp_executesql if you want to return a variable value using dynamic sql

see example under using OUTPUT parameter in below link

http://technet.microsoft.com/en-us/library/ms188001.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MCPietersTP
Starting Member

15 Posts

Posted - 2013-05-21 : 06:44:12

I don't want to return a variable, i want to return a sql statement, in which i use a variable, the variable gets its value from a field in the first table and then this value is used in the second table, the variable gets the name of the database with which must be joined.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 06:48:18

You cant get variable populated with a value inside dynamic sql unless you use sp_executesql as suggested

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources