Wednesday, March 21, 2012

Problem with @@identity in SQL Server 6.5

In SQL Server 6.5, I have a table called employee
with two fields emp_id and emp_name where emp_id
is an identity column.

The below Stored Procedure is trying to select
the last inserted emp_id using @.@.identity.

CREATE PROCEDURE Employee AS

begin

SET IDENTITY_INSERT employee OFF

SET NOCOUNT ON

declare @.empid int

INSERT INTO employee(emp_name) VALUES
("Soundy")

SET @.empid = @.@.identity

return @.empid

end

But when compiling this procedure, SQL Server displays
an error saying "Incorrect syntax near @.empid".

Can i know where the problem is ?Q1 Can i know where the problem is ?

A1 Try 'Soundy' instead of "Soundy"? For example:

Use TempDB
Go

CREATE TABLE [employee] (
[emp_id] [int] IDENTITY (1, 1) NOT NULL ,
[emp_name] [nvarchar] (50) NULL)
Go

CREATE PROCEDURE ins_Employee
@.pEmpName nvarChar (50) AS
begin
SET IDENTITY_INSERT employee OFF
SET NOCOUNT ON
declare @.empid int
INSERT INTO employee(emp_name) VALUES
(@.pEmpName)
SET @.empid = @.@.identity
return @.empid
end

Go

DECLARE @.RC int
-- exec the Proc
EXEC @.RC = ins_Employee @.pEmpName = 'Soundy'
Select @.RC as '@.RC for ins_employee'
Go

SELECT emp_id, emp_name
FROM employee
Where
emp_id = (SELECT Max(emp_id)FROM employee)

No comments:

Post a Comment