MS SQL Error converting datatype varchar to numeric


Below I have a stored procedure that I am currently working on, When I run this stored procedure i get the following error:

Msg 8114, Level 16, State 5, Procedure SP_plottotals, Line 26
Error converting data type varchar to numeric.

Open in new window

What am I doing wrong? The plot#_total column in the database is set to decimal(18,2) as the data type.

I am trying to select the SUM of a column from a table into a variable in the first statement and then updating another column with the value of the SUM variable in the second statement.

The Procedure is:

  [SP_plottotals] 243, 1
ALTER PROCEDURE [dbo].[SP_plottotals]
	-- Add the parameters for the stored procedure here
     @PKCPID int
    ,@plot int

DECLARE @i int
DECLARE @sql nvarchar(500)
DECLARE @plottotal decimal(18,2)

set @i = 1
WHILE (@i <= (400))

set @sql = '
SELECT '+@plottotal+' = SUM(plot'+cast(@plot as varchar(100))+'_total) FROM tblprojects_plantlist WHERE FKCPID='+cast(@PKCPID as varchar(100))+''

print @sql

exec sp_executesql @sql


DECLARE @i2 int
DECLARE @sql2 nvarchar(500)

set @i2 = 1
WHILE (@i2 <= (400))

set @sql2 = '
	plot'+cast(@plot as varchar(100))+'_total = '+cast(@plottotal as varchar(200))+'
WHERE FKCPID='+cast(@PKCPID as varchar(100))+''

print @sql2

exec sp_executesql @sql2


Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Don't know, but it looks like the WHILE loop is not incrimenting @i2 before the END, so this would be an infinite loop.   Need to add this before the END

SET @i2 = @i2 + 1
Brian CroweDatabase AdministratorCommented:
The variable @plottotal should be inside the quoted area:

set @sql = 'SELECT @plottotal = SUM(plot'+cast(@plot as varchar(100))+'_total) FROM tblprojects_plantlist WHERE FKCPID='+cast(@PKCPID as varchar(100))

I believe you will have other issues beyond this as is mentioned above but this should get you past the error.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SevronAuthor Commented:
Thank you for your help. I've implemented both of your answers which helped and managed to fix the new problem myself. Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.