update DB table from stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

create procedure update_sam1(@control_id as varchar) as
begin
update Table_2 set Current_Value=10 where Control_Id=1

end

hi im just trying to update a "Current_Value" in the table using stored procedure but the table is not getting updated... im new to stored procedure... please help...

Posted 23-May-12 5:12am

Member 8711310

Add a Solution

Comments

Ganesan Senthilvel 23-May-12 11:14am

Run SQL Profiler tool to debug the DB operations

3 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)

Ganesan Senthilvel · Answer 1 · 2012-05-23T05:17:00

Solution 1

Put select statement before and after update query, to validate the result

SQL

create procedure update_sam1(@control_id as varchar) as
begin
select Current_Value, Control_Id from Table_2 where Control_Id=1
update Table_2 set Current_Value=10 where Control_Id=1
select Current_Value, Control_Id from Table_2 where Control_Id=1
end

Posted 23-May-12 5:17am

Ganesan Senthilvel

Comments

Member 8711310 23-May-12 11:32am

thanks for the reply... problem still remains... DB not updating...

Maciej Los · Answer 2 · 2012-05-23T07:04:00

I suppose Current_value and Control_Id fields are numerics (integers).

Try this:

SQL

CREATE PROCEDURE UpdateCurrentValueById 
	-- Add the parameters for the stored procedure here
	@ci int = 0, 
	@cv int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE Table2 SET [Current_Value] = @cv WHERE [Control_Id] = @ci
	--return updated value
	SELECT @cv = [Current_Value] FROM Table2 WHERE [Control_Id] = @ci
END
GO

Wendelius · Answer 3 · 2012-05-23T08:30:00

Do you really want to use a static value in your procedure's WHERE clause. What I mean is that instead of

SQL

create procedure update_sam1(@control_id as varchar) as
begin
   update Table_2 set Current_Value=10 where Control_Id=1
end

should you have

SQL

create procedure update_sam1(@control_id as varchar) as
begin
   update Table_2 set Current_Value=10 where Control_Id=@control_id
end

So the parameter passed would control what row is updated. Don't know if this is what you're after but currently you don't use the parameter at all.

update DB table from stored procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0