Solved

Database as variable

Posted on 2010-08-13
20
219 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
  • 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
 

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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:ScottPletcher
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:ScottPletcher
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now