CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-update
Dillon Burke
Dillon Burke

Reputation: 49

Using an UPDATE Stored Procedure

I am currently trying to create a stored procedure that will be used to UPDATE a table. I am running into an issue where the code says it executes, affects every line but nothing updates. I want to be able to update information in a single row at a time and have the row actually update.

Code below:

CREATE PROCEDURE UpdateTrail  
    (@Open char(3),
     @AmountReal int,
     @AmountFake int) 
AS 
BEGIN 
    UPDATE TRAIL
    SET @Open = TOpen, 
        @AmountReal = AmountRealSnow, 
        @AmountFake = AmountFakeSnow

    RETURN @@Identity 
 END

My columns are the following in the trail table:

TrailID int identity primary key,
TrailName varchar(255) Unique, 
Topen char(3),
AmountReal int,
Amountfake int

Please, any suggestions?

Upvotes: 1

Views: 12850

Answers (1)

xDJR1875
xDJR1875

Reputation: 280

Generally the structure of an update statement s/b

UPDATE myTable
   SET myColumn = @variable
     , myCol2   = @var2
 WHERE myTable.filtercolumn = @variable

As suggested in comments by Sami, you will also need to pass in the record key to the stored procedure for the record to be updated.

something like this:

CREATE PROCEDURE UpdateTrail (@Key int, 
@Open              char(3),
@AmountReal        int,
@AmountFake        int
) 
AS 
BEGIN 
    Update TRAIL
    set TOpen = @Open
      , AmountRealSnow = @AmountReal
      , AmountFakeSnow = @AmountFake 
    Where TRAIL.TrailID = @Key
    Return @@Identity 
 END

Upvotes: 2

Related Questions