SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 joining with db where dbname is stored in field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 05/21/2013 :  05:53:19  Show Profile  Reply with Quote
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

Netherlands
15 Posts

Posted - 05/21/2013 :  05:57:08  Show Profile  Reply with Quote
Because now the only message i get is: Commands completed successfully
and no results
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  05:58:02  Show Profile  Reply with Quote
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
Go to Top of Page

MCPietersTP
Starting Member

Netherlands
15 Posts

Posted - 05/21/2013 :  06:44:12  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  06:48:18  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000