wheres error in my code

Carnation Flowers

9y913

Create Proc GetFeesValues

@RateValue decimal(2,18),

@allfees decimal (2,18),

@Mcfees decimal (2,18),

@Docfees decimal (2,18),

@Comfees decimal(2,18),

@MCID int

begin

( (select RateValue from dbo.rates where MCID=@MCID)

Set @Appfees=(@allfees*@RateValue)/100

SET @Docfees=(@allfees*@RateValue)/100

Set @Mcfees=(@allfees*@RateValue)/100

insert into dbo.fees(feesapp,feesdoc,feesmc,allfees,McID) values(@Appfees,@Docfees,@Mcfees,@allfees,@MCID))

end

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
try this below code
Create Proc GetFeesValues
@allfees numeric(18,2),
@Comfees numeric(18,2),
@MCID int
as
begin
declare @RateValue  numeric(18,2),@Appfees numeric(18,2) ,@Docfees numeric(18,2), @Mcfees numeric(18,2);
set @RateValue=(select RateValue from dbo.rates where MCID=@MCID)
Set @Appfees=(@allfees*@RateValue)/100
SET @Docfees=(@allfees*@RateValue)/100
Set @Mcfees=(@allfees*@RateValue)/100
insert into dbo.fees(feesapp,feesdoc,feesmc,allfees,McID) values(@Appfees,@Docfees,@Mcfees,@allfees,@MCID)
end
Ramesh Barik9y ago
-- Exec GetFeesValues 12.10,2

ALTER PROC GetFeesValues (
@allfees DECIMAL(18, 2)
,@MCID INT
)
AS
BEGIN
BEGIN TRY
DECLARE @RateValue DECIMAL(18, 2)
,@Appfees DECIMAL(18, 2)
,@Mcfees DECIMAL(18, 2)
,@Docfees DECIMAL(18, 2)
,@Comfees DECIMAL(18, 2);
SELECT @RateValue = RateValue
FROM dbo.rates
WHERE MCID = @MCID
SET @Appfees = (@allfees * @RateValue) / 100
SET @Docfees = (@allfees * @RateValue) / 100
SET @Mcfees = (@allfees * @RateValue) / 100
INSERT INTO dbo.fees (
feesapp
,feesdoc
,feesmc
,allfees
,McID
)
VALUES (
@Appfees
,@Docfees
,@Mcfees
,@allfees
,@MCID
)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE();
END CATCH
END

Check this SP and let me know if found any issue

Thanks
Carnation Flowers9y ago
i did declared it
but no benfit same error canot to see it
Ramesh Barik9y ago
Sorry I forget to delclare the variable
@Appfees decimal(18,2)

Can u please specify the error which will help me to overcome the issue for you.

Thanks
Carnation Flowers9y ago
same error
i declared the Appfees
but doesnt appear
Ramesh Barik9y ago
If u can share your issue then it wouble better to explain.

As per you stored procedure all the paramters are mandatory. Any one parameter value is not passed from application then it will throw error.

But why u have written this statement.
( (select RateValue from dbo.rates where MCID=@MCID)
As per this stament it only select the ratevalue of of one MCID and but it not any use inside this stored procedure.

If any one parameter you dont wnat to pass from the application then u can set it as Null. Ex: @RateValue decimal(18,2)=null.

As per my understnding from the stored procedure you need to pass ony two paramer.
@allfees and @MCID int.

Create Proc GetFeesValues(
@allfees decimal (18,2),
@MCID int)
as
begin
Declare
@RateValue decimal(18,2),
@Mcfees decimal (18,2),
@Docfees decimal (18,2),
@Comfees decimal(18,2);

select @RateValue=RateValue from dbo.rates where MCID=@MCID
Set @Appfees=(@allfees*@RateValue)/100
SET @Docfees=(@allfees*@RateValue)/100
Set @Mcfees=(@allfees*@RateValue)/100
insert into dbo.fees(feesapp,feesdoc,feesmc,allfees,McID) values(@Appfees,@Docfees,@Mcfees,@allfees,@MCID))
end

Check this SP if it can help u.
Carnation Flowers9y ago
i changed the the decimal value
but still in error
are there any example like my code
its about get the value of DocFees an MC fees after insert all fees
if there trigger its ok
Ramesh Barik9y ago
This issue is due to the precision value is more that the decmal value.
It may be equal or less than
Ie.

Decimal (18,2) or Decimal(10,10). But not Decimal(5,15).

Changed the sp.

Create Proc GetFeesValues
@RateValue decimal(18,2),
@allfees decimal (18,2),
@Mcfees decimal (18,2),
@Docfees decimal (18,2),
@Comfees decimal(18,2),
@MCID int
as
begin
( (select RateValue from dbo.rates where MCID=@MCID)
Set @Appfees=(@allfees*@RateValue)/100
SET @Docfees=(@allfees*@RateValue)/100
Set @Mcfees=(@allfees*@RateValue)/100
insert into dbo.fees(feesapp,feesdoc,feesmc,allfees,McID) values(@Appfees,@Docfees,@Mcfees,@allfees,@MCID))
end
Carnation Flowers9y ago
yes
Msg 192, Level 15, State 1, Procedure GetFeesValues, Line 3
The scale must be less than or equal to the precision.
Msg 137, Level 15, State 2, Procedure GetFeesValues, Line 11
Must declare the scalar variable "@MCID".
Msg 137, Level 15, State 2, Procedure GetFeesValues, Line 12
Must declare the scalar variable "@allfees".
Msg 137, Level 15, State 2, Procedure GetFeesValues, Line 13
Must declare the scalar variable "@allfees".
Msg 137, Level 15, State 2, Procedure GetFeesValues, Line 14
Must declare the scalar variable "@allfees".
Msg 137, Level 15, State 2, Procedure GetFeesValues, Line 17
Must declare the scalar variable "@Appfees".
Ramesh Palanivel9y ago
HI amani,

Is your stored Procedure showing any error or Result is Invalid?