Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    Chennai, India
    Posts
    10

    Unanswered: 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 ?

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •