Found the problem - Unable to use SCOPE_IDENTITY()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Found the problem - Unable to use SCOPE_IDENTITY()

Author

Topic

mparkhouse
Starting Member

5 Posts

Posted - 2010-03-07 : 19:37:17

I left out the END for the whole procedure. I copied it incorrectly. I have the following code in a stored proc:

CREATE PROCEDURE rcr_insertResident
-- Add the parameters for the stored procedure here
@Identifier_Type char(2),
@Identifier varchar(30),
@Full_Name varchar(80),
@Suffix varchar(4),
@DOB datetime,
@DL_Number varchar(30),
@DL_State varchar(2),
@Change_User varchar(10),
@Change_date datetime,
@Change_PropID varchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert Resident (Identifier_Type,
Identifier,
Full_Name,
Suffix,
DOB,
DL_Number,
DL_State,
Change_User,
Change_date,
Change_PropID)
values (@Identifier_Type,
@Identifier,
@Full_Name,
@Suffix,
@DOB,
@DL_Number,
@DL_State,
@Change_User,
@Change_date,
@Change_PropID)
select SCOPE_IDENTITY()
GO

When I try to build the stored proc, I get the following error:

Msg 102, Level 15, State 1, Procedure rcr_insertResident, Line 40
Incorrect syntax near ')'.

The table Resident has one identity field Resident_Key which is also the primary key.

I've looked around the web and can't find a reason why this isn't working.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 23:50:28

see second method using output parameters here

http://www.sqlteam.com/article/stored-procedures-returning-data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-08 : 03:11:53

What is your objective? What are you trying to accomplish?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources