Database as variable

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

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
DhaestCommented:
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
 
LowfatspreadCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Larry Bristersr. DeveloperAuthor Commented:
Also...the select statement I'm using is actually populating a #temp table that is immediatelly killed when the report has been printed.
0
 
BodestoneCommented:
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
 
Lara FEACommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
BodestoneCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
BodestoneCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
BodestoneCommented:
Don't forget it was Dhaest who gave you the solution. I just helped out with a couple of minor details.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Dhaest: and Bodestone:
You guys mind a 350 /150 split?
0
 
Scott PletcherSenior DBACommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
BodestoneCommented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
Larry Bristersr. DeveloperAuthor Commented:
Bodestone:
You ok with the 150?  I'd like to close this question
0
 
BodestoneCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Excellent job folks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.