Stored Procedure to divide two columns and store the result

Vikash Kumar

9y2.5kSQL

Hi,

Hi I am looking fto create a stored procedure in mssql which divides two column values and stores the result in another. Can we use cursor?

Table should look like

Employee

Basic pay(monthly) Number of days Basics per day

5000 30 166

So the data type here should be int

Any help would be really appreciated

8 Replies

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

JASPAL9y ago
Accepted answer
create proc getdata
as
begin
select basic_pay,number_of_days,(basic_pay/number_of_days) as basic_per_day from Table_name
end
Nitin Sontakke9y ago
If you want to do the calculation at time of inserting...it should come in values part. Your coming in columns part.

See my other post for the details.
Vikash Kumar9y ago
Ramesh Palanivel, thanks for your question, I want to make these calculations while insertng records.
Ramesh Palanivel9y ago
Hi Vikas Kumar,

I have one doubts inyour question , Kindly confirm,

a. Do you want make these calculation while you inserting record in table or retrieving "Basic Pay" and "Number of days" from other table?
Nitin Sontakke9y ago
Your parameter names MUST comply with the rules of identifiers.

https://msdn.microsoft.com/en-in/library/ms175874.aspx
Nitin Sontakke9y ago
I strongly discourage you from using "spoken English words" as column names. Everything has its place and that should be respected.

The way you have defined columns and objects, you will need to put square brackets [] everywhere.

Your column name SHOULD BE column name. It CANNOT have a formula.

insert into table_name (list of columns) values (list of corresponding values) is the syntax.

In the list of columns place no formulas are allowed.
Vikash Kumar9y ago
This a stored procedure I tried but it shows error for divide formula

CREATE Procedure [dbo].[Insert_PreRegitrationRate]
(
@[PA-1] INTEGER,
@[Number of patient encounters pre-registered],
@[Number of scheduled patient encounters] INTEGER,
@[Pre-Registration Rate] INTEGER,
@Location VARCHAR(50),
@Specialty varchar(50),
)

as
Begin
IF EXISTS(SELECT [PA-1] FROM [Pre-Registration Rate])
begin
Insert into Pre-Registration Rate(
[PA-1],[Number of patient encounters pre-registered],[Number of scheduled patient encounters],([Number of patient encounters pre-registered]/[Number of scheduled patient encounters])*100 as [Pre-Registration Rate],Location,Specialty)
values(
@[PA-1],@[Number of patient encounters pre-registered],@[Number of scheduled patient encounters],@[Pre-Registration Rate],@Location,@Specialty)
set @ReturnValue=1
end
begin
set @ReturnValue=0
end
select @ReturnValue=SCOPE_IDENTITY()
End
GO

List of errors

Msg 102, Level 15, State 1, Procedure Insert_PreRegitrationRate, Line 4
Incorrect syntax near 'INTEGER'.
Msg 102, Level 15, State 1, Procedure Insert_PreRegitrationRate, Line 17
Incorrect syntax near '('.
Msg 137, Level 15, State 1, Procedure Insert_PreRegitrationRate, Line 20
Must declare the scalar variable "@ReturnValue".
Msg 137, Level 15, State 1, Procedure Insert_PreRegitrationRate, Line 23
Must declare the scalar variable "@ReturnValue".
Msg 137, Level 15, State 1, Procedure Insert_PreRegitrationRate, Line 25
Must declare the scalar variable "@ReturnValue".
Nitin Sontakke9y ago
Do you mean something like this?

select [basic_pay]
,[number_of_days]
,[basic_pay] / [number_of_days] [basic_per_day]
from employee

Stored Procedure to divide two columns and store the result

Ramesh Palanivel, thanks for your question, I want to make these calculations while insertng records.