Passing in DB name as a Parameter
Hey guys,
I need to dynamically build the database name I am going to query from.
For instance I have a bunch of Org_xxxx databases that I need to retrive data from.
SELECT Count(user_id)
From Org_xxxx.dbo.Assignment_Requests
Where Org_xxxx.dbo.Assignment_Requests.org_id=@Org_ID)
The Org_ID parameter is passed in to the procedure.
How do i do this:
Select From
'Org_' + Cast(@Org_ID, char(4)).dbo.assignment_requests
Thanks,
Mike Ross
Blessed is the man who fears the LORD, who delights greatly in his commandments. Psalms 112:1
Peter J. Boettcher posted this at 01:34 — 9th November 2002.
They have: 812 posts
Joined: Feb 2000
You're using sprocs?
If so it's pretty easy, just build your SQL string in the sproc, for example:
CREATE PROCEDURE dbo.SprocName
(
@Org_ID integer
)
AS
SET NOCOUNT ON
DECLARE @Query varchar(1000)
SET @Query = 'SELECT COUNT(user_id) FROM Org_'
+ CAST(@Org_ID, char(4)) + '.dbo.Assignment_Requests
WHERE Org_' + CAST(@Org_ID, char(4)) + '.dbo.Assignment_Requests.org_id = ' + CAST(@Org_ID, char(4))
EXECUTE(@Query)
GO
That should do it. Just make sure you validate the Org_ID otherwise it will fubar your sproc.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.