Stored Procedure to divide two columns and store the result

Question

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

JASPAL · Answer

create proc getdata as begin select basic_paynumber_of_days(basic_pay/number_of_days) as basic_per_day from Table_name end

Nitin Sontakke · Answer

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.

Ramesh Palanivel · Answer

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 Sontakke · Answer

Your parameter names MUST comply with the rules of identifiers. https://msdn.microsoft.com/en-in/library/ms175874.aspx

Nitin Sontakke · Answer

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 Kumar · Answer

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]LocationSpecialty) 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 Sontakke · Answer

Do you mean something like this? select [basic_pay] [number_of_days] [basic_pay] / [number_of_days] [basic_per_day] from employee

Vikash Kumar · Answer

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