Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
  • 9
  • 6
  • 2
  • +3
2 Solutions
 
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
 
lrbristerAuthor 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor Commented:
Excellent job folks!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now