problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
error on the Set line. How can I fix it?
create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Set @.e = Select @.@.Identity
return
go
Thanks,
Rich"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich
Set @.e = @.@.Identity
or
Select @.e = @.@.Identity
Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.|||The offending line should be
SELECT @.e = @.@.IDENTITY
however, why create a stored procedure to get @.@.IDENTITY, when you can just
retrieve its value within a batch using SELECT @.@.IDENTITY?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||Thanks. From what I understand Scope_Identity works within a specified scop
e
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 row
s
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.
May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@.microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>
set @.a = scope_identity()
scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@.@.identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.sql

Problem with query

Hello everyone.
Does anybody have an idea how to optimize following?

I have a table - containing primary key (not identity) wich - on a new entry- should always be the lowest possible number (e.g. 1-234 and 236-400 are occupied - it should select 235, not 401).

Currently I'm doing it trough a dlookup loop searching for a free number (lowest possible)...

Is there any sql query (select top 1 or something like that) to speed this up?

Thanks for any replies!
Greetz - Marcselect min(key) from yourtable

ask yourself why you want to fill in the gaps

a primary key should have no meaning

a surrogate key (an assigned number, for instance) should not even be visible to users of the application

rudy
http://rudy.ca/|||Dear Rudy,
Select min(key) would give me back the lowest existing number - i need the lowest free number.
Primary Key was a bit wrong told - it has a identity besides this number, but i need this number to be unique, and given each time as low as possible (not taken)...

I'm using the system to make reservations in the local database - e.g. article ... gets reserved for a customer under reservation number 5, 1-4 and 6-10 are taken...|||doh!! (smacks self on head)

sorry, i answered too quickly, of course min(key) doesn't get the lowest available number

i could give you some sql, but it involves a left outer join with a temp table containing every integer smaller than max(key) -- it would be ugly and slow

as i said, you should re-think why you want the number to "fill in the gaps"

rudy|||Dear Rudy,
I need it to be the lowest possible number - else it would fill up my whole office :).
I've thought about this reservation system because you always have a low number (max) and so you can sort in the articles by reservation number - wich wouldn't exceed 1000 (except if really more than one thousand would be reserved)...
You can imagine it like this:
I have a wall full of articles with numbers, reserved for customers...
If I would have a ongoing number, the wall would have to get bigger and bigger (if I sort them in ascending by number)...

Do you think it would be faster if I do a SELECT * on a recordset object and loop trough than dlookup?|||consider this --create table reservations
(id integer primary key
, title varchar(50) not null
);
insert into reservations (id, title)
values (1, 'the first one');
insert into reservations (id, title)
values (937, 'the second one');
insert into reservations (id, title)
values (2, 'the third one');how big is your table? three rows

trust me, you do not have to re-use numbers to prevent your table from growing

the database does not reserve space for missing entries

;)|||Dear Robert,
It doesn't consider me if the table grows - the wall with the reservations would have to grow if i reserve by number...

e.g. pos 1 is number 1 - until pos 600 reserved... wall is full sorted by reservation number... now if I don't re-use the numbers wich go out (e.g. 50 gets sent)... my numbers will go to 1200 sometime... and i cannot store that reservation number in my office :)...|||let me explain otherwise...
i have a wall - with places for articles (reservations) - this wall is numbered from 1 to 1000 - so i need the reservations go from 1 to max 1000 - i need to reuse those numbers, elsewhere i would have to make the wall bigger ...
so what I do currently, is loop trough the table with a counter looking for a free position...|||create a new table for your reservation numbers, 1 through 600 or whatever (you can even use auto_number for this)

create table reservationnumbers
( resno integer not null
, reservationid integer null
)

reservationid will point to a reservation your existing table of reservations, the ones that can get deleted -- go ahead and assign them with an autonumber

when you want to assign a new reservation, use

select min(resno) from reservationnumbers
where reservationid is null

when you delete a reservation, make sure you set the corresponding reservationid in the the reservationnumbers table to null

rudy|||You could create a simple table called 'ALLVALUES' with only a numeric field (MYVALUE) containing all possible values (Ex. 1 - 5000).
Obviously this field must be the primary key.

then you can create a query extracting the lowest value in your table not matching with values in ALLVALUES.

Select min(ALLVALUES.MYVALUE) from ALLVALUES
LEFT OUTER JOIN YOURTABLE on (
ALLVALUES.MYVALUE = YOURTABLE.Key )
WHERE YOURTABLE.Key IS NULL

I think this will work

Bye
movendra@.yahoo.com|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||Let us say your table is called XXX and the column that you are seek to find the lowest unused value is COL_ID

select min(x1.COL_ID + 1)
from XXX x1 (nolock)
where not exists
(
select *
from XXX x2 (nolock)
where x2.COL_ID = (x1.COL_ID + 1)
)|||That solution didn't get in my mind at all - thanks guys! I'll try it this way...

thanks for the help!|||May this will be more understandable for you...

create table test(id int, name varchar(10))

insert test values(1,'1')
insert test values(3,'3')
insert test values(4,'4')
insert test values(5,'5')

select min(id+1) newid from test
where (id+1) not in (select id from test)

newid
----
2|||hey guys (snail and achorozy), that's pretty slick

unfortunately it doesn't find the gap at the front of the table, but i suppose that's a minor quibble, eh

:cool: