What is the problem with this code? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What is the problem with this code?

Author

Topic

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-09 : 11:32:34

If i have Debug=0 it runs fine but when i set debug=1 i get error saying

Ownership:Insert Error: Column name or number of supplied values does not match table definition. I dont know what is wrong with the select statements in the debug=1 condition...you can just run it no dependancies

ALTER PROC [dbo].[UP_Ownership_total1] ( @retVal VARCHAR(500) OUTPUT)
AS
BEGIN TRY
DECLARE @module varchar(20)
SET @module='Ownership'
DEclare @num_of_years int
set @num_of_years=5
DECLARE @stateFees_module varchar(20)
SET @stateFees_module='State Fees'

--IF DEBUG=0,debug is OFF and if DEBUG=1, debug mode is ON
DECLARE @DEBUG int
SET @DEBUG=1
DECLARE @handle INT
DECLARE @vc_trimid int,
@year int,
@vmp_w_rebate DECIMAL(10,4),
@destination DECIMAL(10,4),
@residual_type nvarchar(15),
@actual_annual_mileage DECIMAL(10,4),
@state nvarchar(50),
@fuel_inflation DECIMAL(10,4),
@city_percent DECIMAL(10,4),
@highway_percent DECIMAL(10,4),
@fuel_type nvarchar(5),
@city_fuel nvarchar(5),
@highway_fuel nvarchar(5),
@gas_guzzler_charge DECIMAL(10,4),
@insurance_experience char,
@intrest DECIMAL(10,4),
@ending_period int,
@percent_down DECIMAL(10,4),
@beginning_mileage DECIMAL(10,4),
@labor_rate DECIMAL(10,4),
@free_maintenance_miles DECIMAL(10,4)

--print the input values
IF @DEBUG=1
BEGIN
SELECT vctrim=@vc_trimid,year=@year,vmpwrebate=@vmp_w_rebate,destination=@destination,actAnnMil=@actual_annual_mileage,
state=@state,fuelinflation=@fuel_inflation,citypercent=@city_percent
SELECT highwaypercent=@highway_percent,@fuel_type,@city_fuel,@highway_fuel,@gas_guzzler_charge,@intrest,@ending_period,@percent_down,@beginning_mileage
SELECT laborrate=@labor_rate,@free_maintenance_miles
END
--Create a temp table
Create Table #Results(
[vc_trimid] [int] NULL,
[cost_type] [nvarchar](30) NULL,
[year] [smallint] NULL,
[year_total] [decimal](10, 4) NULL,
[month1] [decimal](10, 4) NULL,
[month2] [decimal](10, 4) NULL,
[month3] [decimal](10, 4) NULL,
[month4] [decimal](10, 4) NULL,
[month5] [decimal](10, 4) NULL,
[month6] [decimal](10, 4) NULL,
[month7] [decimal](10, 4) NULL,
[month8] [decimal](10, 4) NULL,
[month9] [decimal](10, 4) NULL,
[month10] [decimal](10, 4) NULL,
[month11] [decimal](10, 4) NULL,
[month12] [decimal](10, 4) NULL,
[datetime] [smalldatetime] NULL
)
--Insert rows in to the temp table
INsert into #Results values(2000,'Test Cost',2010,12000,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000,1000,current_Timestamp)
Select * from #Results

END TRY
BEGIN CATCH
SET @RetVal = @module +':'+ ERROR_MESSAGE()

IF @DEBUG=1
SELECT RetVal=@RetVal

RAISERROR(@RetVal, 16, 1)

END CATCH

+++++++++++++++++++++++++++++++
Can be run with
+++++++++++++++++++++++++++++
Declare @retVal varchar(255)
declare @t table
([vc_trimid] [int] NULL,
[cost_type] [nvarchar](255) NULL,
[year] [smallint] NULL,
[year_total] [decimal](10, 4) NULL,
[month1] [decimal](10, 4) NULL,
[month2] [decimal](10, 4) NULL,
[month3] [decimal](10, 4) NULL,
[month4] [decimal](10, 4) NULL,
[month5] [decimal](10, 4) NULL,
[month6] [decimal](10, 4) NULL,
[month7] [decimal](10, 4) NULL,
[month8] [decimal](10, 4) NULL,
[month9] [decimal](10, 4) NULL,
[month10] [decimal](10, 4) NULL,
[month11] [decimal](10, 4) NULL,
[month12] [decimal](10, 4) NULL,
[datetime] [smalldatetime] NULL
)

INSERT @t(vc_trimid,cost_type,year,year_total,month1,month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12,datetime)
EXEC [dbo].[UP_Ownership_total1] @retVal OUTPUT
select @retVal
select * from @t

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-09 : 12:37:53

The resultset, returned by your SP, should be inserted in @t - right?

This:
IF @DEBUG=1
BEGIN
SELECT vctrim=@vc_trimid,year=@year,vmpwrebate=@vmp_w_rebate,destination=@destination,actAnnMil=@actual_annual_mileage,
state=@state,fuelinflation=@fuel_inflation,citypercent=@city_percent
SELECT highwaypercent=@highway_percent,@fuel_type,@city_fuel,@highway_fuel,@gas_guzzler_charge,@intrest,@ending_period,@percent_down,@beginning_mileage
SELECT laborrate=@labor_rate,@free_maintenance_miles
END
returns additional resultsets so the insert will not work

No, you're never too old to Yak'n'Roll if you're too young to die.

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-09 : 12:50:51

Thanks figured it out..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-09 : 12:55:53

You're welcome.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources