SQL Stored Procedure

Question

1.00/5 (2 votes)

See more:

Hi,

I have created a table "Employee" and inserted few datas into it and then created a procedure but it didnt execute,please correct my code and explain me where i went wrong. the commands are as follows.

SQL

Create table Employee(
Employee_id int NOT NULL PRIMARY KEY,
Employee_firstname varchar(20),
Employee_surname varchar(20) NOT NULL,
Address varchar(30)NOT NULL,
Designation varchar(20) NOT NULL,
Salary decimal(6,2),
Department_id int NOT NULL,
Department_name varchar(20) NOT NULL 
--constraint pk_employee_id PRIMARY KEY(Employee_id),
--constraint pk_department_id PRIMARY KEY(Department_id)
)

Insert into Employee values(001, 'Rakesh', 'Panda','CP3,sector-8,HCL Technology','Support Analyst',3.5,10,'IT')
Insert into Employee values(002, 'Monalisa', 'Mohanty','CP3,sector-8,HCL Technology','Program Analyst',4.5,10,'IT')
Insert into Employee values(003, 'Ajay', 'Kumar','CP3,sector-8,HCL Technology','Employee Partner',3.2,20,'HR')
Insert into Employee values(004, 'Nikhil', 'Srivastav','CP3,sector-8,HCL Technology','Transport Manager',3.0,30,'Admin')
Insert into Employee values(005, 'Parul', 'Mehta','CP3,sector-8,HCL Technology','project Lead',6.5,10,'IT')

Create procedure raise_salary(
@id INT,
@salary decimal OUT,
@percent decimal)
AS 
begin
update Employee
set Salary = Salary*(1+ percent/100)
where Employee_id = @id
End

Thanks
Rakesh

Posted 27-May-13 9:12am

Member 10079259

Updated 27-May-13 9:22am

Maciej Los

v2

Add a Solution

Comments

[no name] 27-May-13 15:25pm

You will probably have to be a bit more specific than "didnt execute". You have not shown anything that tried to execute your SP so we could not tell you where you "went wrong".

Maciej Los 27-May-13 15:25pm

where i went wrong - this is not informative at all...
Please, be more specific and provide more details (expected output).

frostcox 27-May-13 15:26pm

Hey your not executing your stored procedure anywhere!

Rob Philpott 27-May-13 17:16pm

Yes, if you tell us the error then it would save a lot of guesswork. But as we are guessing, perhaps you need a 'go' after the create statements?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

prasantcode · Answer 1 · 2013-05-27T19:39:00

Solution 1

Hi Panda
Please find the solution .
just u did mistake in passing param

SQL

-- exec raise_salary 1,1.2,2

Create procedure raise_salary(
@id INT,
@salary decimal OUT,
@percent decimal)
AS 
begin
update Employee
set Salary = Salary*(1+ @percent/100)
where Employee_id = @id
End

Posted 27-May-13 19:39pm

prasantcode

Updated 27-May-13 21:34pm

v3

Comments

gvprabu 28-May-13 3:37am

Check your Code.... How u will get OUTPUT Parameter in SP.?

gvprabu 28-May-13 3:42am

He need to get the Updated Salary in OUTPUT Parameter...

gvprabu · Answer 2 · 2013-05-27T21:42:00

Hi,

Check t he following Code, I think it will help you...

SQL

CREATE PROCEDUREe raise_salary(
@id INT,
@salary DECIMAL OUT,
@percent DECIMAL)
AS 
BEGIN
	DECLARE @FinalSalary DECIMAL

	-- Calculate Salary
	SELECT @FinalSalary = Salary*(1+ @percent/100) WHERE Employee_id = @id

	-- Update the Salary
	UPDATE Employee SET Salary = @FinalSalary WHERE Employee_id = @id

	-- Assign to OUT parameter
	SELECT @salary=@FinalSalary  
END 

-- Execute Statement
DECLARE @Salary DECIMAL
EXEC raise_salary @id=123, @salary=@salary OUTPUT, @percent =1.35
SELECT @Salary

Regards
GVPRabu

Saranya Boopathi · Answer 3 · 2013-05-29T23:45:00

Solution 3

--Exec raise_salary 3,'4.50',5.5

ALter procedure raise_salary(
@id INT,
@salary decimal OUT,
@percent decimal)
AS 
begin
update Employee
set Salary = Salary*(1+ @percent/100)
where Employee_id = @id
End

change percent into @percent

Posted 29-May-13 23:45pm

Saranya Boopathi

amnk.info · Answer 4 · 2013-05-30T01:36:00

Solution 4

change your parameter into @percent.

Create procedure raise_salary(
@id INT,
@salary decimal OUT,
@percent decimal)
AS
begin
update Employee
set Salary = Salary*(1+ @percent/100)
where Employee_id = @id
End

Posted 30-May-13 1:36am

amnk.info