Error converting data type varchar to float. when trying to run stored procedure

When I try and run the stored procedure below, it is breaking on line 67. I believe this is caused by the following line:

set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.BalMarkPrem + ' + @BalMarkPrem + ')) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABalMarkPrem, '

This is the error that I am getting:

Msg 8114, Level 16, State 5, Procedure ShowCogsDetail, Line 67
Error converting data type varchar to float.

USE [Pricing_test3]
GO
/****** Object:  StoredProcedure [dbo].[ShowCogsDetail]    Script Date: 11/10/2008 14:11:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Mario Hernandez
-- Create date: 08/01/08
-- Description:	Show Cogs Detail
-- =============================================
ALTER PROCEDURE [dbo].[ShowCogsDetail] 
	-- Add the parameters for the stored procedure here
	@ProspectID int = 0,
	@DelayStart int = 0,
	@Term		int = 0,
	@BPRatew	float = 0,
	@BalMarkPrem float = 0
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
	Declare @x as int
	Declare @sqlCmd as varchar(max)
	set @sqlCmd = ''
 
	set @x = 1
	While @x < 61 
	IF @Term = @x 
	BEGIN
		Begin													--
			set @sqlCmd = @sqlCmd + 'SELECT ' + Cast(@x as varchar) + ' as Terms, tbl_Allocation.ProspectID, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * ' + @BPRatew + ') / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABPRatew, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Ancillaries) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAAncillaries, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.BPFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABPFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Ercot_AssessFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAErcot_AssessFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.TexasRec_rate + tbl_Cogs.Ren1_rate + tbl_Cogs.Ren2_rate) * PCtGreen) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAGreen, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.LineLoss) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WALineLoss, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Hedging) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAHedging, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.BalMarkPrem + ' + @BalMarkPrem + ')) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABalMarkPrem, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Less25mwPrem) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WASmall, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.MeterFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAMeterFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.IntraDayMu) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAIntraDayMu, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Total) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WATotal '
			set @sqlCmd = @sqlCmd + ' FROM         tbl_Allocation LEFT OUTER JOIN '
			set @sqlCmd = @sqlCmd + ' tbl_Prospect ON tbl_Allocation.ProspectID = tbl_Prospect.ProspectID LEFT OUTER JOIN '
			set @sqlCmd = @sqlCmd + ' tbl_Cogs ON tbl_Prospect.ProspectID = tbl_Cogs.ProspectID '
			set @sqlCmd = @sqlCmd + ' WHERE     (tbl_Cogs.Fwd_Mth between 1 + ' + Cast(@DelayStart as varchar) + ' and ' + Cast(@x as varchar) + ' + ' + cast(@DelayStart as Varchar) + ')   and tbl_Prospect.ProspectID = ' + Cast(@ProspectID as varchar) + ' and tbl_Allocation.[End] is Null '
			set @sqlCmd = @sqlCmd + ' GROUP BY tbl_Allocation.ProspectID, tbl_Allocation.[End] '
			
			set @x = @x + 1 
			if @x < 61
				set @sqlCmd = @sqlCmd + ' Union '
 
 
		--Select @sqlcmd
		END
	END
	ELSE
	BEGIN
		Begin													--
			set @sqlCmd = @sqlCmd + 'SELECT ' + Cast(@x as varchar) + ' as Terms, tbl_Allocation.ProspectID, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.BPRatew) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABPRatew, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Ancillaries) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAAncillaries, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.BPFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABPFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Ercot_AssessFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAErcot_AssessFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.TexasRec_rate + tbl_Cogs.Ren1_rate + tbl_Cogs.Ren2_rate) * PCtGreen) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAGreen, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.LineLoss) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WALineLoss, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Hedging) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAHedging, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.BalMarkPrem + ' + @BalMarkPrem + ')) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABalMarkPrem, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Less25mwPrem) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WASmall, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.MeterFee) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAMeterFee, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.IntraDayMu) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WAIntraDayMu, '
			set @sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * tbl_Cogs.Total) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WATotal '
			set @sqlCmd = @sqlCmd + ' FROM         tbl_Allocation LEFT OUTER JOIN '
			set @sqlCmd = @sqlCmd + ' tbl_Prospect ON tbl_Allocation.ProspectID = tbl_Prospect.ProspectID LEFT OUTER JOIN '
			set @sqlCmd = @sqlCmd + ' tbl_Cogs ON tbl_Prospect.ProspectID = tbl_Cogs.ProspectID '
			set @sqlCmd = @sqlCmd + ' WHERE     (tbl_Cogs.Fwd_Mth between 1 + ' + Cast(@DelayStart as varchar) + ' and ' + Cast(@x as varchar) + ' + ' + cast(@DelayStart as Varchar) + ')   and tbl_Prospect.ProspectID = ' + Cast(@ProspectID as varchar) + ' and tbl_Allocation.[End] is Null '
			set @sqlCmd = @sqlCmd + ' GROUP BY tbl_Allocation.ProspectID, tbl_Allocation.[End] '
			
			set @x = @x + 1 
			if @x < 61
				set @sqlCmd = @sqlCmd + ' Union '
 
 
		--Select @sqlcmd
		END
	END
 
	Exec(@sqlCmd)
 
End

Open in new window

LVL 3
utlonghornjulieAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hello utlonghornjulie,

The data type of the parameter/variable @BalMarkPrem is most likely float and so SQL sees the + which should be concatenting that value to your dynamic SQL string as addition of two floats however the string with SQL statements can't be converted to float since they are T-SQL and not numbers.  To fix, you must cast the paramater to varchar so that SQL knows to concatenate as string.

sqlCmd = @sqlCmd + ' Cast(SUM(tbl_Cogs.MwhAct * (tbl_Cogs.BalMarkPrem + ' + CAST(@BalMarkPrem AS VARCHAR(20)) + ')) / SUM(tbl_Cogs.MwhAct) as Dec(8,3) ) AS WABalMarkPrem, '

Best regards,

mwvisa1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.