I have a function that is executed when an access report is run and I need to be able to tell the function what the tablename is where the data is located. I can send the table name to the function but I can't use the exec command within the function.
ALTER FUNCTION dbo.qfcEmployeeGoalsR1New
RETURNS @Goals_Table TABLE (StoreID int, EmployeeNumber int, Name varchar(50), fldActualSales float, StoreDescription varchar(50),
fldGoalSales float, YTDGoals float, YTDActuals float, MTDGoals float, MTDActuals float, WTDGoals float,
WTDActuals float, WTDRate float, Period1 int, Period2 int, Period3 int, Period4 int, Year1 int, Year2 int,
YearCurrent int, WeekCurrent int, T13Goals float, T13Actuals float)
declare @tsqlstring varchar(350)
set @tsqlstring = 'INSERT INTO @Goals_Table
SELECT GM.Storeid, GM.EmployeeNumber, GM.Name, GM.fldActualSales, GM.StoreDescription, GM.fldGoalSales, GM.YTDGoals,
GM.YTDActuals, GM.MTDGoals, GM.MTDActuals, GM.WTDGoals, GM.WTDActuals, GM.WTDRate, GM.Period1,
GM.Period2, GM.Period3, GM.Period4, GM.Year1, GM.Year2, GM.YearCurrent, GM.WeekCurrent, GM.T13Goals, GM.T13Actuals from ' + @tablename + ' GM'
I realize now that I cannot use a exec within a function but because multiple people are running the reports I need to be able to create the data within a stored procedure and write it to a unique table name for each user.
Oct 03 '10 at 02:52 PM