May 10, 2006 at 11:39 am
Hi all,
I tried to use the SQL Server Managemet Studio-Express Edition to execute the following code statements:
--- SQLQuery.sql---
USE testDB
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType
varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType
varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
//////////////////////////////////////////////////////////////////////////////////////////////////
I got the following T-SQL error messages:
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10
Incorrect syntax near the keyword 'PROC'.
Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11
Incorrect syntax near the keyword 'PROC'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14
Incorrect syntax near 'EXE'.
Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20
Incorrect syntax near 'updateRecord'.
/////////////////////////////////////////////////////////////////////////////////////////////////////
Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem.
Thanks,
Scott Chang
May 11, 2006 at 7:20 am
As the error message says, you need to use the batch delimiter "GO" to seperate your create proc statement into different batch.
May 11, 2006 at 8:33 am
Hi peterhe,
Your response is not helpful to me. I feel I have the batch delimiter "GO" in the end of the program. Where should I add more batch delimiter "GO"?
I am new in T-SQL and this is my first time to do/learn the Creating and Using Stored Procedures by copying the code statements from a book. Please give more details and be more specific in your instructions to solve this problem.
Thanks in advance,
Scott Chang
May 11, 2006 at 9:06 am
Hello peterhe,
After I posted my last response, I tried to add more batch delimiter "GO" to my program and executed my revised program (listed below) and I got new error messages (also listed below). Note: I have an existing table "dbo.Inventory" that has 3 rows of data in my database "testDb". Please help and advise again.
Thanks,
Scott Chang
/////////////////////////////////////////////////////////
--SQLQueryRev1.sql---
USE testDb
GO
DECLARE @procID int
DECLARE @procName varchar(20)
DECLARE @procType varchar(20)
CREATE PROC sp_getRecords AS SELECT * FROM Inventory
GO
CREATE PROC sp_insertRecord @procID int, @procName varchar(20), @procType varchar(20) AS INSERT INTO Inventory VALUES (@procID, @procName, @procType)
GO
CREATE PROC sp_deleteRecord @procID int AS DELETE FROM Inventory WHERE ID=@procID
GO
CREATE PROC sp_updateRecord @procID int, @procName varchar(20), @procType varchar(20) AS UPDATE Inventory SET name=@procName, type=@procType where ID=@procID
GO
--- Insert records into the Inventory table
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
EXE sp_insertRecord 5, 'ER', 'VHS'
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
--- Delete record with ID=3
EXEC sp_deleteRecord 3
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
--- View the updated table
EXEC sp_getRecords
GO
///Results//////
Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 4
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '4'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'updateRecord'.
May 11, 2006 at 11:56 am
Did you try the following?:
EXE sp_insertRecord 4, 'ER Vol 1', 'DVD'
GO
EXE sp_insertRecord 5, 'ER', 'VHS'
GO
EXE sp_insertRecord 6, 'Sixth Sense', 'DVD'
GO
--- Delete record with ID=3
EXEC sp_deleteRecord 3
GO
--- Update record with ID=5
EXEC sp-updateRecord 5 'ERVol1', 'VHS'
GO
--- View the updated table
EXEC sp_getRecords
GO
May 11, 2006 at 1:34 pm
Hi barsuk,
No, I did not do the way you suggested. Instead, I deleteled the 3 DECLARE statements and corrected 1 small mistake (missing a coma in EXEC sp_updatedRecord 5, 'ER Vol1', 'VHS' and ). It works fine now.
Scott Chang
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply