Solved

Creating an Update stored procedure - SQL Server 2005

Posted on 2011-02-20
4
1,045 Views
Last Modified: 2012-05-11
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
Comment
Question by:98fatboyrider
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 250 total points
ID: 34938302
Your where clause is wrong missing the @ before ID, chenge to :

WHERE MovieID = @ID
0
 

Author Comment

by:98fatboyrider
ID: 34938338
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
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 250 total points
ID: 34938359
remove the closing bracket from here :

Picture=@Picture)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34938371
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now