Return ID - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Return ID

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-10 : 11:02:29

Dear All,
How can I return the Inserted Call ID(Not an identityColumn)


Create Procedure sp_CallBooking
{
  @Du nvarchar(10),
  @Call_Nature_ID nvarchar(10),
  @Call_Super_Category_ID nvarchar(10),
  @Asset_ID nvarchar(10),
  @Call_Booked_By nvarchar(10),
  @Call_Booked_From nvarchar(50),
  @Call_Description nVarchar(500),
  @Call_status char(1),
  @CallID nvarchar(10) OUTPUT
as 
 Insert into ITAdmin(Call_id,DU,Call_Nature_ID,Call_SuperCategory_ID,
 Asset_ID,Call_Booked_By,Call_Booked_From,Call_Description,Call_Status)
values(Select ISNULL(Max(Call_ID),0)+1,@DU,@Call_Nature_ID,@Call_Super_Category_ID,
@Asset_ID,@Call_Booked_By,@Call_Booked_From,@Call_Description,@Call_status)

}

Thanks In Advance
Dana

Kristen
Test

22859 Posts

Posted - 2007-07-10 : 11:10:04

This might work, but I expect there is an opportunity for the ID to have been allocated already in another process.


Create Procedure sp_CallBooking
{
  @Du nvarchar(10),
  @Call_Nature_ID nvarchar(10),
  @Call_Super_Category_ID nvarchar(10),
  @Asset_ID nvarchar(10),
  @Call_Booked_By nvarchar(10),
  @Call_Booked_From nvarchar(50),
  @Call_Description nVarchar(500),
  @Call_status char(1),
  @CallID nvarchar(10) int OUTPUT
as 
SELECT	@Call_id = ISNULL(Max(Call_ID), 0)+1
FROM	ITAdmin

INSERT INTO ITAdmin
(
	Call_id, DU, Call_Nature_ID, Call_SuperCategory_ID,
	Asset_ID, Call_Booked_By, Call_Booked_From, Call_Description, Call_Status
)
VALUES
(
	@Call_id, @DU, @Call_Nature_ID, @Call_Super_Category_ID,
	@Asset_ID, @Call_Booked_By, @Call_Booked_From, @Call_Description, @Call_status)
}

Kristen

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-10 : 11:15:52

quote:
This might work, but I expect there is an opportunity for the ID to have been allocated already in another process.

Dear Kristen,
What do you mean by the above one.Please be brief

Dana

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-10 : 11:15:55

quote:
This might work, but I expect there is an opportunity for the ID to have been allocated already in another process.

Dear Kristen,
What do you mean by the above one.Please be brief

Dana

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-07-10 : 15:13:30

Dana,

Kristen means that it's possible for another application/thread to be called between


SELECT	@Call_id = ISNULL(Max(Call_ID), 0)+1
FROM	ITAdmin

and here:


INSERT INTO ITAdmin
(
	Call_id, DU, Call_Nature_ID, Call_SuperCategory_ID,
	Asset_ID, Call_Booked_By, Call_Booked_From, Call_Description, Call_Status
)
VALUES
(
	@Call_id, @DU, @Call_Nature_ID, @Call_Super_Category_ID,
	@Asset_ID, @Call_Booked_By, @Call_Booked_From, @Call_Description, @Call_status)
}

which could possibly cause two Call_ids to have the same value.

You really should use an identity column for this.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-10 : 17:06:26

I think an IDENTITY column would be a better solution, because it is more scalable, and less likely to cause attempts to insert duplicate rows.

However, since this is SQL Server 2005, you can take advantage of a new feature, the OUTPUT clause, to do what you want

This code shows how it works. Read SQL Server 2005 Books Online for more details on how to use the OUTPUT clause.


create table MyTemp ( MyInt int not null )
go
insert into MyTemp
	(
	MyInt
	)
output
	inserted.Myint
select
	MYInt = ( select isnull(max(MyInt),0)+1 from MyTemp )

insert into MyTemp
	(
	MyInt
	)
output
	inserted.Myint
select
	MYInt =  ( select isnull(max(MyInt),0)+1 from MyTemp )

insert into MyTemp
	(
	MyInt
	)
output
	inserted.Myint
select
	MYInt =  ( select isnull(max(MyInt),0)+1 from MyTemp )

go

drop table MyTemp


Results:
Myint       
----------- 
1

(1 row(s) affected)

Myint       
----------- 
2

(1 row(s) affected)

Myint       
----------- 
3

(1 row(s) affected)

CODO ERGO SUM

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-07-11 : 00:11:31

Dear Kirstan/KenW,
If i use the Transaction object will it reallocate id ?
Dana

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-07-11 : 05:16:39

If really do not want to use IDENTITY then a transaction with the use of UPLOCK will stop attempts at duplicate entries. As has already been indicated, this does not help throughput!

CREATE PROCEDURE sp_CallBooking
{
  @Du nvarchar(10),
  @Call_Nature_ID nvarchar(10),
  @Call_Super_Category_ID nvarchar(10),
  @Asset_ID nvarchar(10),
  @Call_Booked_By nvarchar(10),
  @Call_Booked_From nvarchar(50),
  @Call_Description nVarchar(500),
  @Call_status char(1),
  @CallID nvarchar(10) int OUTPUT
AS

DECLARE @Call_id int

BEGIN TRY

    BEGIN TRANSACTION

    SET @Call_id = (
            SELECT ISNULL(Max(Call_ID), 0) + 1
            FROM ITAdmin WITH (UPDLOCK)
        )

    INSERT INTO ITAdmin
    (
        Call_id, DU, Call_Nature_ID, Call_SuperCategory_ID,
        Asset_ID, Call_Booked_By, Call_Booked_From, Call_Description, Call_Status
    )
    VALUES
    (
        @Call_id, @DU, @Call_Nature_ID, @Call_Super_Category_ID,
        @Asset_ID, @Call_Booked_By, @Call_Booked_From, @Call_Description, @Call_status)
    }

    COMMIT

END TRY

BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK

    -- raise error etc

END CATCH

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-07-11 : 07:07:10

Also, naming your stored procs with "sp_" is a no-no. This makes SQL Server look in the Master DB for the proc first, no matter what. (Or it did in 2k. Wonder if it changed for 2k5).

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources