I need advice as to what route to take ...
We have an HR search using dynamic sql in a sproc.
The sproc uses a view to pull back all possible search criteria that might be need by user searching HR information.
Pulls back all Employee information fine.
On the other side we have the same dynamic sql in a sproc doing same sort of search from Intranet DB.
The sproc uses a table that is refreshed with data about meeting room locations and if they are available and contractors names and details.
if this information was stored in the new HR system we wouldnt need this cross search.
We are trying to work out the best way to do this .
Suggestions please on following:
Run a sproc that runs HR search and inserts into a temp table all results
This sproc also runs similar Intranet search and stores the ROOM and CONTRACTOR details in same temp table.
We then distinct this temp table as part of sproc
Then a Search sproc called StaffSearch - points to this temp table and using dynamic sql shows whats searched on to user.
Be it HR type details like employee information or Room/Contractor information.