x

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)
AS
    BEGIN

         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)

    RETURN
    END

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

svallc gravatar image

svallc
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

ozamora gravatar image

ozamora
1.4k 2 3 5

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x41
x21

asked: Oct 03, 2010 at 02:52 PM

Seen: 1036 times

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