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