Solved

Database as variable

Posted on 2010-08-13
20
224 Views
Last Modified: 2012-05-10
I have a stored procedure that needs to look at one of two databases on my Server Instance.

How can I change the code below to allow me to pass in the database name as a variable?

The tables have the same names...


FROM    CRMPROD_01.dbo.P_Deals p LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID

Open in new window

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 2
  • +3
20 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 350 total points
ID: 33429264
This can be done by means of dynamic sql. use a variable to store dbname name and use this variable to built sql string like

SELECT @Sql ='SELECT fields FROM ' + @dbname +'.Table'

then use EXEC() or sp_executesql to execute the sql string.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 33429438
what distinguishes the data on each server?

could you create a distributed view over the tables and so not have to worry about which server held the data?

what is your actual data scenario?
0
 

Author Comment

by:lrbrister
ID: 33429480
Lowfatspread:
We deal with two entities in our business model.
  Structured Settlements and Lottery
By law the databases cannot integrate data.  The "views" issue is a gray area because we're only creating a reporting system.
..but when it comes to the Feds we'd rather not take a chance
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:lrbrister
ID: 33429492
Also...the select statement I'm using is actually populating a #temp table that is immediatelly killed when the report has been printed.
0
 
LVL 10

Expert Comment

by:Bodestone
ID: 33429513
There is also the option of using a condition. It means the query would have to be maintained in 2 placed but might make it more readable than using a dynamic query.

In addition, if going down the dynamic route it may also be safter to use square braces in case the DB name needs them due to a character such as a hypen which would not be liked. Also, son't forget the schema.

SELECT @Sql ='SELECT fields FROM [' + @dbname +'].dbo.Table'
DECLARE @db sysname
SET @db = 'CRMUAT_01'

IF	@db = 'CRMUAT_01'
BEGIN
	SELECT	p.*
	FROM    CRMUAT_01.dbo.P_Deals p LEFT JOIN    
		CRMUAT_01.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		CRMUAT_01.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID
END
ELSE IF @db = 'CRMPROD_01'
BEGIN
	SELECT	p.*
	FROM    CRMPROD_01.dbo.P_Deals p LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID

Open in new window

0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33429541
Can you create 2 copies or SP on each server and then call proc as
exec dbName..procName
i
or define 2 connection strings in your application and exec proc on one or another connection
0
 

Author Comment

by:lrbrister
ID: 33429543
My attached query in dynamic sql runs and I get the "1256842" records created message.
However...
I can't access the #temp table.
Any ideas?

declare @sql nvarchar(MAX)
declare @dbname nvarchar(30)
set @dbname = 'CRMPROD_01'

set @sql = '
SELECT  p.Rep,pet.EventType,pet.eventDescription,    
YEAR(pe.EventData) as YR,    
                sum(case when MONTH(pe.EventData)=1 then 1 else 0 end) as Jan,    
                sum(case when MONTH(pe.EventData)=2 then 1 else 0 end) as Feb,    
                sum(case when MONTH(pe.EventData)=3 then 1 else 0 end) as Mar,    
                sum(case when MONTH(pe.EventData)=4 then 1 else 0 end) as Apr,    
                sum(case when MONTH(pe.EventData)=5 then 1 else 0 end) as May,    
                sum(case when MONTH(pe.EventData)=6 then 1 else 0 end) as Jun,    
                sum(case when MONTH(pe.EventData)=7 then 1 else 0 end) as Jul,    
                sum(case when MONTH(pe.EventData)=8 then 1 else 0 end) as Aug,    
                sum(case when MONTH(pe.EventData)=9 then 1 else 0 end) as Sept,    
                sum(case when MONTH(pe.EventData)=10 then 1 else 0 end) as Oct,    
                sum(case when MONTH(pe.EventData)=11 then 1 else 0 end) as Nov,    
                sum(case when MONTH(pe.EventData)=12 then 1 else 0 end) as Dec,  
                                count(pe.EventData) as YTD into #temp
FROM    ' + @dbname + '.dbo.P_Deals p LEFT JOIN    
		' + @dbname + '.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		' + @dbname + '.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID    
Where pet.active = ''True'' and pe.EventData is not null and pe.EventData <> ''''
Group by p.Rep,pet.EventType,pet.eventDescription,year(pe.EventData),pet.EventTypeID  
order by p.rep, pet.EventTypeID'

exec (@sql)

Open in new window

0
 
LVL 10

Assisted Solution

by:Bodestone
Bodestone earned 150 total points
ID: 33429569
Yeah. The temp table is created within the session created by exec. When that session ends the temp table is dropped

You can do

INSERT #temp
exec (@sql)

but would need to create the temp table manually first.
0
 

Author Comment

by:lrbrister
ID: 33429591
Bodestone:
  Ok...final question...
This data is usually @ 10,000 records at most.
Is there any reason why I can't just do an insert into temp and then drop the table afterwards?
Does that start making a significant log or db size issue?
0
 
LVL 10

Expert Comment

by:Bodestone
ID: 33429684
Not sure I follow what you mean there. Do you mean insert all the data from both databases into a temp table with an additional column of DBName and then query it with a parameter?

Temp tables are always dropped when the connection that created them ends. This is why when creating a temp table within an exec statement it cannot be accessed afterwards because it is automatically dropped when that statement is finished.

In your dynamic SQL you can insert into ##temp which would continue to exest outside the exec statement but you would then have to explicitly drop it to be certain.
0
 

Author Comment

by:lrbrister
ID: 33429714
Bodestone:
 
Perfect...didn't know about the ##
Heres's my final insert...I drop the ##temp at the end of the stored procedure
 

declare @sql nvarchar(MAX)
set @sql = '
SELECT p.Rep,pet.EventType,pet.eventDescription,
YEAR(pe.EventData) as YR,
sum(case when MONTH(pe.EventData)=1 then 1 else 0 end) as Jan,
sum(case when MONTH(pe.EventData)=2 then 1 else 0 end) as Feb,
sum(case when MONTH(pe.EventData)=3 then 1 else 0 end) as Mar,
sum(case when MONTH(pe.EventData)=4 then 1 else 0 end) as Apr,
sum(case when MONTH(pe.EventData)=5 then 1 else 0 end) as May,
sum(case when MONTH(pe.EventData)=6 then 1 else 0 end) as Jun,
sum(case when MONTH(pe.EventData)=7 then 1 else 0 end) as Jul,
sum(case when MONTH(pe.EventData)=8 then 1 else 0 end) as Aug,
sum(case when MONTH(pe.EventData)=9 then 1 else 0 end) as Sept,
sum(case when MONTH(pe.EventData)=10 then 1 else 0 end) as Oct,
sum(case when MONTH(pe.EventData)=11 then 1 else 0 end) as Nov,
sum(case when MONTH(pe.EventData)=12 then 1 else 0 end) as Dec,
count(pe.EventData) as YTD
into ##temp
FROM ' + @dbname + '.dbo.P_Deals p LEFT JOIN
' + @dbname + '.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN
' + @dbname + '.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID
Where pet.active = ''True'' and pe.EventData is not null and pe.EventData <> ''''
Group by p.Rep,pet.EventType,pet.eventDescription,year(pe.EventData),pet.EventTypeID
order by p.rep, pet.EventTypeID'
exec (@sql)
 
0
 
LVL 10

Expert Comment

by:Bodestone
ID: 33429756
Don't forget it was Dhaest who gave you the solution. I just helped out with a couple of minor details.
0
 

Author Comment

by:lrbrister
ID: 33429781
Dhaest: and Bodestone:
You guys mind a 350 /150 split?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33429940
Just to let you know:

You can avoid dynamic SQL and/or duplicate queries by putting the procedure in the master db, naming it beginning with "sp_", and using a system proc to mark it as a system object.  It will then automatically reference whatever db it's called from.
0
 

Author Comment

by:lrbrister
ID: 33430088
ScottPletcher:  Thanks...but this stored procedure is in a REPORTS Database and needs to reference any one of 5 databases on the same SQL server Instance.
 
0
 
LVL 10

Expert Comment

by:Bodestone
ID: 33430103
Aye but given the circumstances I thought in this instance messing with the master DB could get messy. Also the procedure looks like it is being called from a reporting DB to fetch data from 2 other DBs.

I'm happy with whatever Dhaest is. If he wants more it is fine by me.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 33430464
>> but this stored procedure is in a REPORTS Database and needs to reference any one of 5 databases on the same SQL server Instance. <<

That's exactly what I'm talking about.  If the code is in the master db, it can reference any db on that server **without changing the code in any way**.

It's not really "messy", you just have to document that it's there.  But I can understand if you don't want to use it.  Then again, you might reconsider after having errors for the third time because your dupliicate copies of the code got out of sync :-) .
0
 

Author Comment

by:lrbrister
ID: 33430539
Bodestone:
You ok with the 150?  I'd like to close this question
0
 
LVL 10

Expert Comment

by:Bodestone
ID: 33430853
I'm happy with 150. Cheers.

Scott.

What the issue is here is, as you say, with a system procedure it operated on the database it is being called from.

The procedure will be called from databaseA but wants to retrieve data from databaseB or databaseC

With the dynamic SQL solution there is only one set of code to maintain.

0
 

Author Closing Comment

by:lrbrister
ID: 33430928
Excellent job folks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question