Conversion Failure 2

[MJV57]

The following code gives me this error:
Msg 245, Level 16, State 1, Procedure PriceDiscCalcECS, Line 61
Conversion failed when converting the varchar value '=@quantity ' to data type int.

Not sure why quantity is becoming a varchar or how to change it back any help would be appreciated.


USE [Estimator]
GO
/****** Object: StoredProcedure [dbo].[PriceDiscCalcECS] Script Date: 04/27/2009 15:43:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Michael Ventrella
-- Create date: April 27,2009
-- Description: Price Calculatation For EC's
-- =============================================
ALTER PROCEDURE [dbo].[PriceDiscCalcECS]
-- Add the parameters for the stored procedure here
@estnum int = 0,
@quantity int =0,
@procprice decimal =0,
@forprocprice decimal =0,
@simprice decimal =0,
@forsimprice decimal =0,
@dsgnprice decimal =0,
@fordsgnprice decimal =0,
@2cprice decimal =0,
@for2cprice decimal =0,
@2dprice decimal =0,
@for2dprice decimal =0,
@3cprice decimal =0,
@for3cprice decimal =0,
@3dprice decimal =0,
@for3dprice decimal =0,
@assyprice decimal =0,
@forassyprice decimal =0,
@spotprice decimal =0,
@forspotprice decimal =0,
@tryprice decimal =0,
@fortryprice decimal =0,
@cmmprice decimal =0,
@forcmmprice decimal =0,
@prochrs decimal =0,
@simhrs decimal =0,
@dsgnhrs decimal =0,
@2chrs decimal =0,
@2dhrs decimal =0,
@3chrs decimal =0,
@3dhrs decimal =0,
@assyhrs decimal =0,
@spothrs decimal =0,
@tryhrs decimal =0,
@cmmhrs decimal =0,
@totalhrs decimal =0,
@materialprice decimal =0,
@formatprice decimal =0,
@transport decimal =0,
@fortransport decimal =0,
@homeline decimal =0,
@forhomeline decimal =0,
@diediscount decimal =0,
@cdnpricesum decimal =0,
@cdndiscpricesum decimal =0,
@forgnpricesum decimal =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
INSERT INTO QuoteCalcBody
(estno, quantity, totalmaterialcost, fortotalmaterialcost, totalsimhrs, totaldsgnhrs, total2chrs, total2dhrs, total3chrs, total3dhrs, totalassyhrs,
totalspothrs, totaltryhrs, totalcmmhrs, totalhrs, processprice, forprocessprice, simulationprice, forsimulationprice, designprice, fordesignprice, price2c,
forprice2c, price2d, forprice2d, price3c, forprice3c, price3d, forprice3d, assyprice, forassyprice, spotprice, forspotprice, tryprice, fortryprice, cmmprice,
forcmmprice, diediscount, pricecdnprediscount, pricecdnpostdiscount, priceforeignpostdiscount)
SELECT estno, '=cast(@quantity as int)' AS Expr1, '=@totalmaterial' AS Expr2, '=@formaterial' AS Expr3, '=@simhrs' AS Expr4, '=@dsgnhrs' AS Expr5,
'=@c2hrs' AS Expr6, '=@2dhrs' AS Expr7, '=@3chrs' AS Expr10, '=@3dhrs' AS Expr8, '=@assyhrs' AS Expr9, '=@spothrs' AS Expr11,
'=@tryhrs' AS Expr12, '=@cmmhrs' AS Expr13, '=@totalhrs' AS Expr14, '=@processprice' AS Expr15, @forprocprice AS Expr18,
'=@simulationprice' AS Expr16, @forsimprice AS Expr19, '=@designprice' AS Expr17, @fordsgnprice AS Expr20, @2cprice AS Expr21,
@for2cprice AS Expr22, @2dprice AS Expr23, @for2dprice AS Expr24, @3cprice AS Expr25, @for3cprice AS Expr26, @3dprice AS Expr27,
@for3dprice AS Expr28, @assyprice AS Expr29, @forassyprice AS Expr30, @spotprice AS Expr31, @forspotprice AS Expr32, @tryprice AS Expr33,
@fortryprice AS Expr34, @cmmprice AS Expr35, @forcmmprice AS Expr36, @diediscount AS Expr37, @cdnpricesum AS Expr38,
@cdndiscpricesum AS Expr39, @forgnpricesum AS Expr40
FROM QuoteCalcBody AS QuoteCalcBody_1
WHERE (estno = @estnum)


END

 

[gmmastros]

It appears as though you are trying to concatenate a string. When you do this, you need to be careful about data types. Based on SQL's built-in rules for data type precedence, when you combine a string with an int (or any other number type), sql attempts to convert to the number (not a string).

Ex:

Declare @TestVar Int

Set @TestVar = 1

Select 'Blah Blah String Data' + @TestVar

If you run that, you will get the error:
[red]Conversion failed when converting the varchar value 'Blah Blah String Data' to data type int.[/red]

If you want a string, you need to force the conversions of your numbers, like this...

Declare @TestVar Int

Set @TestVar = 1

Select 'Blah Blah String Data' + Convert(VarChar(10), @TestVar)

The knowledge base article from microsoft explaining this:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom