• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1060
  • Last Modified:

Creating an Update stored procedure - SQL Server 2005

I'm trying to create a stored procedure which will update a single record in a table. The code below is not working. Any advice would be appreciated.
USE [D:\MOVIESDB\MOVIEDB.MDF]
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE usp_update_movie
(@ID int,
 @Title varchar(50),
 @Director varchar(50),
 @YearReleased smallint,
 @Genre varchar(50),
 @Stars smallint,
 @Plot varchar(100),
 @IsDVD varchar(10),
 @RunTime smallint,
 @Rating varchar(10),
 @Picture varchar(50))
AS
SET NOCOUNT ON;

UPDATE tblMovies
SET 
Title=@Title,
Director=@Director,
YearReleased=@YearReleased,
Genre=@Genre,
Stars=@Stars,
Plot=@Plot,
IsDVD=@IsDVD,
RunTime=@RunTime,
Rating=@Rating,
Picture=@Picture)

WHERE MovieID = ID

Open in new window

0
98fatboyrider
Asked:
98fatboyrider
  • 2
2 Solutions
 
Paul JacksonSoftware EngineerCommented:
Your where clause is wrong missing the @ before ID, chenge to :

WHERE MovieID = @ID
0
 
98fatboyriderAuthor Commented:
Thanks Jacko, I missed that.
I'm still getting the error below. Why I don't know.

"Msg 102, Level 15, State 1, Procedure usp_update_movie, Line 28
Incorrect syntax near ')'."
0
 
Paul JacksonSoftware EngineerCommented:
remove the closing bracket from here :

Picture=@Picture)
0
 
aikimarkCommented:
You have an extraneous right parenthesis character.

Change this

Rating=@Rating,
Picture=@Picture)

Open in new window


To this

Rating=@Rating,
Picture=@Picture

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now