HELP to write stored procedure whose values are calculated automatically in database

Hi frdz,

I m creating my web-application in asp.net with C# 2005 and using sql server 2005.

I have created the stored procedure for the insert,update.

I want to know how to write the mathematical calculations in the stored procedure..

Pls tell me from the below stored procedure were i m making the mistake ??

As the discount and the total amount are not calculated by itself...and stored in the database

How to convert the

@.discpercentnumeric(5,2) to

@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...

ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend

Thanxs in adv...

Hello my friend,

It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.

Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.

Kind regards

Scotty

|||

thanxs for the reply...

can u pls help to calculate this for my above stored procedure its not working..

select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster

|||

Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?

select @.discamt=(@.opbal - @.clbal )* @.discpercent/100

should do.

|||

select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only

I tried with this also but don't work pls help me...

if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end

thanxs for ur replies....

|||

Could it be because either @.opbal or @.clbal is null?

I would use the following: -

IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END

SELECT @.Total

Kind regards

Scotty

|||

thanxs very much again...

Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??

What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution

|||

Hello again Sheenaa,

You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.

Kind regards

Scotty