Change Database In Procedure

I work at a medical billing office. We provide billing services for a number of clients. The primary billing software runs on a big AIX system, but for reporting and a few other things we get a daily dump into sql server. The way the dump happens each client get's their own database in sql server. This works great for reports that go to individual clients, but it can be a real pain for internal reports that cover all clients. We end up with sql code that looks something like this:
SQL Code:






Original
- SQL Code




ALTER PROCEDURE ProceName
AS
BEGIN
CREATE TABLE #Temp
(
Client varchar(5),
FieldName varchar(20),
OtherField int
)

DECLARE @Client varchar(5)
DECLARE @SQL varchar(8000)
DECLARE curAll cursor FOR /*SQL Code to get Client list here */
OPEN curAll
FETCH NEXT FROM curAll INTO @Client
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL =
'SELECT ''' + @Client + ''', *'
+ ' FROM dump_' + @Client + '.dbo.TableName t'
+ ' WHERE t.FieldName=''somevalue'''

INSERT INTO #Temp
Exec(@SQL)

FETCH NEXT FROM curAll INTO @Client
END

--send results to application
SELECT * FROM #Temp

--clean up
CLOSE curAll
DEALLOCATE curAll
DROP Table #Temp
END






ALTER PROCEDURE ProceNameASBEGIN    CREATE TABLE #Temp    (       Client varchar(5),       FieldName varchar(20),       OtherField int    )     DECLARE @Client varchar(5)       DECLARE @SQL varchar(8000)    DECLARE curAll CURSOR FOR /*SQL Code to get Client list here */    OPEN curAll    FETCH NEXT FROM curAll INTO @Client    WHILE @@FETCH_STATUS = 0    BEGIN          SET @SQL =             'SELECT ''' + @Client + ''', *'        + ' FROM dump_' + @Client + '.dbo.TableName t'        + ' WHERE t.FieldName=''somevalue'''         INSERT INTO #Temp            Exec(@SQL)          FETCH NEXT FROM curAll INTO @Client    END      --send results to application    SELECT * FROM #Temp     --clean up    CLOSE curAll    DEALLOCATE curAll    DROP TABLE #TempEND


As you can imagine, this is a real pain. There must be someway to dynamically change the current database in the procedure and run the code there directly rather than creating a string and calling out to exec 50+ times. Any ideas?