October 21, 2010 at 10:09 am
I have a poorly designed accounting forecasting table that contains categories, periods and period adjustments. The period and period adjustment represent the months of the year so there's period_forecast1 - period_forecast12 and there's adjustment columns too which are period_adjust1 - period_adjust12. What I'm trying to do is write a .net program displays the current period and period_adjustment and the next 3 months. But I'm having problems with my update stored procedure. Below I've copied my select sp, and update sp. Do you have any suggestions? On my update sp I keep getting an error saying I've supplied too many arguments..... Any suggestions?
Here's the select sp:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author://
-- Create date: 9/30/2010
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[TANYATESTtsDataByMonthPlus3]
-- Add the parameters for the stored procedure here
@CurrentMonth int
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(1000)
set @sql = N'SELECT id, tl_rpt_seq, tl_frc_desc, period_forecast' + convert(varchar(5),@CurrentMonth) + N' AS MonthA' +
N', period_forecast' + (case when (@CurrentMonth + 1) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 1) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 1)) end ) + N' AS MonthB' +
N', period_forecast' + (case when (@CurrentMonth + 2) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 2) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 2)) end )+ N' AS MonthC' +
N', period_forecast' + (case when (@CurrentMonth + 3) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 4) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 3)) end )+ N' AS MonthD' +
N', period_adjust' + CONVERT(varchar(5),@CurrentMonth)+ N' AS Month_adjA' +
N', period_adjust' + (case when (@CurrentMonth + 1) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 1) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 1)) end ) +N' AS Month_adjB' +
N', period_adjust' + (case when (@CurrentMonth + 2) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 2) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 2)) end ) + N' AS Month_adjC' +
N', period_adjust' + (case when (@CurrentMonth + 3) > 12 then (CONVERT(varchar(5),(@CurrentMonth + 3) - 12)) else CONVERT(varchar(5),(@CurrentMonth + 3)) end )+N' AS Month_adjD' +
N' FROM tl_forecast'
exec sp_executesql @sql
END
--exec [TESTtsDataByMonthPlus3] 11
_______________________________________________________________________
Here's what the table looks like:
IDintUnchecked
tl_rpt_seqintChecked
tl_frc_descvarchar(48)Checked
period_forecast1decimal(18, 2)Checked
period_forecast2decimal(18, 2)Checked
period_forecast3decimal(18, 2)Checked
period_forecast4decimal(18, 2)Checked
period_forecast5decimal(18, 2)Checked
period_forecast6decimal(18, 2)Checked
period_forecast7decimal(18, 2)Checked
period_forecast8decimal(18, 2)Checked
period_forecast9decimal(18, 2)Checked
period_forecast10decimal(18, 2)Checked
period_forecast11decimal(18, 2)Checked
period_forecast12decimal(18, 2)Checked
period_adjust1decimal(18, 2)Checked
period_adjust2decimal(18, 2)Checked
period_adjust3decimal(18, 2)Checked
period_adjust4decimal(18, 2)Checked
period_adjust5decimal(18, 2)Checked
period_adjust6decimal(18, 2)Checked
period_adjust7decimal(18, 2)Checked
period_adjust8decimal(18, 2)Checked
period_adjust9decimal(18, 2)Checked
period_adjust10decimal(18, 2)Checked
period_adjust11decimal(18, 2)Checked
period_adjust12decimal(18, 2)Checked
________________________________________________________________
Here's the update:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author://
-- Create date: 10/5/2010
-- Description:Update the tl_Forecast table with varying column names... use dynamic sql
-- =============================================
ALTER PROCEDURE [dbo].[TANYAUpdatetlForecast]
-- Add the parameters for the stored procedure here
--@CurrentMonth int,
--@ForecastColumn1varchar(25), @ForecastValue1 decimal(18,2),
--@ForecastColumn2varchar(25), @ForecastValue2 decimal(18,2),
--@ForecastColumn3varchar(25), @ForecastValue3 decimal(18,2),
--@ForecastColumn4varchar(25), @ForecastValue4 decimal(18,2),
--@AdjColumn1 varchar(25), @AdjValue1 decimal(18,2),
--@AdjColumn2 varchar(25), @AdjValue2 decimal(18,2),
--@AdjColumn3 varchar(25), @AdjValue3 decimal(18,2),
--@AdjColumn4 varchar(25), @AdjValue4 decimal(18,2),
@ForecastCurrentMonthvarchar(25), @MonthA decimal(18,2),
@ForecastCurrentMonthOut1varchar(25), @MonthB decimal(18,2),
@ForecastCurrentMonthOut2varchar(25), @MonthC decimal(18,2),
@ForecastCurrentMonthOut3varchar(25), @MonthD decimal(18,2),
@AdjCurrentMonth varchar(25), @Month_adjA decimal(18,2),
@AdjCurrentMonthOut1 varchar(25), @Month_adjB decimal(18,2),
@AdjCurrentMonthOut2 varchar(25), @Month_adjC decimal(18,2),
@AdjCurrentMonthOut3 varchar(25), @Month_adjD decimal(18,2),
@ID int
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
exec ('Update tl_forecast ' + 'SET '+ @ForecastCurrentMonth +'= '+ @MonthA + ', '
+ @ForecastCurrentMonthOut1 +'= '+ @MonthB + ', '
+ @ForecastCurrentMonthOut2 +'= '+ @MonthC + ', '
+ @ForecastCurrentMonthOut3 +'= '+ @MonthD + ', '
+ @AdjCurrentMonth +'= '+ @Month_adjA + ', '
+ @AdjCurrentMonthOut1 +'= '+ @Month_adjB + ', '
+ @AdjCurrentMonthOut2 +'= '+ @Month_adjC + ', '
+ @AdjCurrentMonthOut3 +'= '+ @Month_adjD + ', '
+ ' WHERE ID = ' + @ID)
END
October 22, 2010 at 8:43 am
Things will start to get a lot simpler if you can use the modulo operator. For example, (@CurrentMonth + 3) % 12 will automatically return 1 if @CurrentMonth is 10. That should get rid of your CASEs and make the whole thing easier to visualise.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply