September 16, 2008 at 6:31 am
I have been writing basic T-SQL for years but have just recently embarked on function and stored procedure authoring. I am having difficulty getting the below very basic function to work.
My table is far from normalized but it's what I have to work with. The table looks like this:
GL_ACCOUNT char(8) PRIMARY KEY
GL_CURRENT_BUDGET_1 integer --REPRESENTS JAN 2008 BUDGET AMOUNT
GL_CURRENT_BUDGET_2 integer
...
GL_CURRENT_BUDGET_12 integer
GL_NEXTYEAR_BUDGET_1 integer --REPRESENTS JAN 2009 BUDGET AMOUNT
GL_NEXTYEAR_BUDGET_2 integer
...
GL_NEXTYEAR_BUDGET_12 integer
I am attempting to sum the values based on the date passed to the function among other variables. My problem is at the commented lines within the WHILE loop:
CREATE FUNCTION dbo.GETYTDBUDGET
(
@thruDate smalldatetime, --e.g. 12/01/08
@currentNext varchar(8), --CURRENT or NEXTYEAR This can be calculated based on GL Reorg date
--and Now() from GL Control Table once it is available (Dustin import via DTS)
@account varchar(8) --e.g. 30806000
)
RETURNS integer
--RETURNS varchar(200)
BEGIN
DECLARE @ctr as int
DECLARE @mysum as int
DECLARE @sql as varchar(200)
DECLARE @temp AS INT
SET @ctr = 1
WHILE @ctr <= MONTH(@thruDate)
BEGIN
SET @sql = 'SELECT GL_' + @currentNext + '_BUDGET_' + CAST(@ctr AS VARCHAR(2)) +
' FROM OD_GLMAS WHERE GL_ACCOUNT = ' + @account
--EXEC @sql
--SET @temp = EXEC(@sql)
SET @mysum = @mysum + @temp
SET @ctr = @ctr + 1
END
RETURN @mysum
--RETURN @sql
END
September 16, 2008 at 7:13 am
You cannot use dynamic SQL in a function.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply