Using Variable Tablename within a function

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.

For example:

 ALTER FUNCTION dbo.qfcEmployeeGoalsR1New
     @Tablename varchar(25)
 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'
          exec (@tsqlstring)

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.

Any ideas

more ▼

asked Oct 03, 2010 at 02:52 PM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A stored procedure will accomplish what you are requesting. Same path, remove "RETURN".

more ▼

answered Oct 03, 2010 at 05:30 PM

avatar image

1.4k 3 20 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 03, 2010 at 02:52 PM

Seen: 1202 times

Last Updated: Oct 04, 2010 at 12:06 AM

Copyright 2018 Redgate Software. Privacy Policy