Get a value back from SQL Server

I can't use a identity field.
Because I need to know the value before I store it in the DB.

>Because I need to know the value before I store it in the DB.
WHY that, please?

Before the record that uses the NextId-value is created, I need to create some records in an additional table that are linked to the main table.
Linking is based on the NextID value.

creating records in additional tables should be created AFTER the main record is created ...

Please believe me that it is not possible in this particular case.
This Q&A probably takes more time than supply me with an answer.

The eternal problem....
It is impossible to do what you are trying to do unless you lock the table (while it is being edited) from other users.
Otherwise its a mess....

exactly.

the problem is this:
say user 1 starts the process to add... procedure called, returns 101 (but the actualy "max" remains 100 so far)
before the session of user 1 calls/committed the second phase (the insert of that value), user 2 starts the same process... and will get back 101 also...

do yourself a favor, and do it the right way, please.

Oracle has an SQL command for locking a table :
LOCK TABLE <Table_Name> IN EXCLUSIVE MODE;

The lock is released at the end of the transaction (Meaning, COMMIT or ROLLBACK).

You can create a Query and set the SQL property for the above command.

For SQL Server:

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

You will have to do some reading.....   :-)

The idea is to update the value within the storedprocedure and then immediately after that return the value within the same procedure.
That should happen in a split second???
All users use the same storedprocedure, so I guess their requests are put in line.

Nicead :
http://stackoverflow.com/questions/57625/how-do-you-lock-tables-in-sql-server-2005-and-should-i-even-do-it

the problem is still the same :-)
table must be locked !

Learn about record locking in SQL Server. Covers optimistic, and pessimistic locking.

Pessimistic and Optimistic Concurrency

In a multi-user environment, there are two models for updating data in a database: optimistic concurrency and pessimistic concurrency.

Pessimistic concurrency involves locking the data at the database when you read it. You essentially lock the database record and don't allow anyone to touch it until you are done modifying and saving it back to the database. Here you have 100% assurance that nobody will modify the record and while you check it have it checked out, out. aAnother person will have to wait until you have made the your changes.

By default, SQL Server controls lock escalation, but you can control it yourself by using lock optimizer hints. Here are some lock escalation hints you may want to consider:

· ROWLOCK This hint guides tells SQL Server to use row-level locking instead of page locks for INSERTS. By default, SQL Server may perform as a page-level lock instead of a less intrusive row-level lock when inserting data. By using this hint, you can guide tell SQL Server to always start out using row-level locking. But, this hint does not prevent lock escalation if the number of locks exceeds SQL Server's lock threshold.

· SERIALIZABLE (equivalent to HOLDLOCK) applies only to the table specified and only for the duration of the transaction, and it will hold a shared lock for this duration instead of releasing it as soon as the required table, data page, row or data is no longer required.

· TABLOCK specifies that a table lock to be used instead of a page or row level lock. This lock will be remained held until the end of the statement.

· TABLOCKX specifies that an exclusive lock will be applied held on the table until the end of the statement or transaction, and will prevent others from reading or updating the table.

· UPDLOCK specifies that update locks will be used instead of shared locks, and will hold the locks until the end of the statement or transaction.

· XLOCK specifies that an exclusive lock be used and kept activated held until the end of the end of the transaction on all data being processed by the statement. The granularity of XLOCK will be adjusted if it is used with the PAGLOCK or TABLOCK hints.

SQL Server 7.0 and SQL Server 2000 Lock Escalation Options

You can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to guide tell SQL Server to use page locking, not row locks, for a specific table:

SP_INDEXOPTION 'INDEX_NAME', 'ALLOWPAGELOCKS', TRUE

When FALSE, page locks are not used. Access to the specified indexes is obtained using row- and table-level locks only.

SP_INDEXOPTION 'INDEX_NAME', 'ALLOWROWLOCKS', TRUE

When FALSE, row locks are not used, . aAccess to the specified indexes is obtained using page- and table-level locks only.

SQL Server 2000 Lock Escalation Options

SP_INDEXOPTION 'INDEX_NAME', 'DISALLOWPAGELOCKS', TRUE

When TRUE, page locks are not used,. aAccess to the specified indexes is obtained using row- and table-level locks, only.

SP_INDEXOPTION 'INDEX_NAME', 'ALLOWROWLOCKS', TRUE

When TRUE, row locks are not used., aAccess to the specified indexes is obtained using page- and table-level locks, only.

When these commands are used, they affect all queries that eaffect these indexes. This command should not be used unless you know, positively, that they always produce the results you desire.

Important

The SQL Server query optimizer automatically makes the correct determination. It is recommended that you do not override the choices the optimizer makes. Disallowing a locking level can affect the concurrency for a table or index adversely. For example, specifying only table-level locks on a large table accessed heavily by many users can affect performance significantly. Users must wait for the table-level lock to be released before accessing the table.

Optimistic concurrency means you read the database record, but don't lock it. Anyone can read and modify the record at anytime and you will take your chances that the record is not modified by someone else before you have a chance to modify and save it. As a developer, the burden is on you to check for changes in the original data (collisions) and act accordingly based on any errors that may occur during the updating e process..

With optimistic concurrency, the application has to check for changes to the original record to avoid overwriting changes. There is no guarantee that the original record has not been changed, because no lock has been placed on that data at its source - the database. Hence, there is the real possibility of losing changes made by another person.

Optimistic Concurrency Strategies

If you are in a performance state-of-mind, there are chances of your chosingchances are you will go with optimistic concurrency. Optimistic concurrency frees up database resources as quickly as possible so that other users and processes can act upon that data as soon as possible.

There are four popular strategies to deal ing with optimistic concurrency:

1. Do Nothing.

2. Check for changes to all fields during update.

3. Check for changes tof modified fields during update.

4. Check for changes to timestamp (row version) during update.

All of these strategies have to deal with the shaping of the Update T-SQL Command sent to the database during the updating of the data. The examples below are not very detailed on purpose and assume a basic understanding of ADO.NET.

Optimistic Concurrency on Update Strategy #1 - Do Nothing

The simplest strategy for dealing with concurrency issues during the updating of data is to do nothing.

The update command will not check for any changes in the data, only specify the primary key of the record to be changed. If someone else changed the data, those changes will more than likely be overwritten:

Update Product
Set
Name = @Name
Where
ID = @ID

One would hope that this means either 1) the application is a single-user application, or 2) the chance of multi-user update collisions is very unlikely and the repercussions of overwriting data is negligible.

Optimistic Concurrency on Update Strategy #2 - Check All Fields

With this strategy, the update command will check that all fields in the row (usually minus BLOB fields) are equal to their original values when performing the update to assure no changes have been made to the original record. A check of the return value of the ExecuteNonQuery Command will indicatetell you if the update actually took place. The return value of the ExecuteNonQuery Command is typically the number of rows affected by the query.

Update Product
Set
Name = @Name,
Where
ID = @ID
AND
Name = @OriginalName
AND
Price = @OriginalPrice

This is essentially what CommandBuilder creates when using DataSets and is a strategy that doesn't want to see any changes to the data.

Optimistic Concurrency on Update Strategy #3 - Check Only Changed Fields

Rather than checking all fields in the row to make sure they match their original value, this strategy checks only those fields whichthat are being updated in the command.

Update Product
Set
Name = @Name
Where
ID = @ID
AND
Name = @OriginalName

This strategy only cares that it is not overwriting any data and could care less thant other fields in the record may have been changed. This could create an interesting combination of data in the row.

Optimistic Concurrency on Update Strategy #4 - Implement Timestamp

SQL Server has a timestamp ( alias rowversion ) field that is modified every time a change is made to a record that contains such a field. Therefore, if you add such a field to a table you only have to verify the timestamp record contains the same original value to be assured none of the fields have been changed in the record.

Update Product
Set
Name = @Name
Where
ID = @ID
AND
TimestampID = @TimestampID

This is the same as Strategy #2 above without the need for checking all fields.

Optimistic concurrency has a performance component to it that suggests a higher performing ASP.NET website.

There are other methods of achieving optimistic concurrency, but I think the ones above are the most popular. A developer needs to look at the application itself to determine which strategy makes sense. The DataSet, Command Builder, and DataAdapter typically handle this stuff for you using Strategy #2. However, if you work with objects instead of DataSets, you need to handle concurrency issues yourself.

Senad:
I've read the posted website. Don't completely understand it though.
Can you advise me on which type of lock to be used.
The table that is accessed only has one row (with some systemwide settings).

Let's try to do it the other way around.

When I store the rows in the cross-reference table first.
Then use the autoinc recordID from the first entry and save that in a separate field that will be used to link these records to the main table.

Crossref table:
ID       LinkedID
1              1
2              1
3              1

Main table:
LinkedID
1

They only problem is that I need to retrieve the ID (autoinc) from the first record that is created.
Does anybody have a solution for that?

Pessimistic
like i said unless you lock the table no way you will get the right ID.

There is something like:
SELECT SCOPE_IDENTITY()
or
@@IDENTITY

But how to use it?

that function returns the last generated id, by an insert in that same session/db connection.

which is exactly what I was referring to:
you should have 1 table with that identity field, and insert there first, and retrieve the id value generated using the
SELECT SCOPE_IDENTITY()
query.  forget about @@identity (unless you use sql 7, where scope_identity does not exist)

"that function returns the last generated id, by an insert in that same session/db connection."
Yes, but then it was suppost that the ID came from the main table. Which isn't available at that time.

Can you give an example on how to accomplish this?

SQL.Add(' INSERT INTO AgendaRelaties ( Customernr ) ');
SQL.Add(' SELECT ' + IntToStr(Customers.CustID) );
 
// Somehow get the ID of the inserted record
LinkedID := ID
SQL.Add(' UPDATE AgendaRelaties SET Linkednr = ' + IntToStr(LinkID));

                                              

1:
2:
3:
4:
5:
6:

Or maybe it's better to do this in a StoredProcedure and call that from within Delphi. I use SQL Server 2005.

Except for the where clause at the end I think it is perfect.

What remains is the question how to execute this from within Delphi?

create procedure do_insert @CustID int, @linked_id int OUTPUT
as
 
 INSERT INTO AgendaRelaties ( Customernr ) VALUES(@custID);
 SET @linked_id = SCOPE_IDENTITY()
 
  UPDATE AgendaRelaties SET Linkednr = @linked_id  WHERE ID = @linked_id
 
                                              

1:
2:
3:
4:
5:
6:
7:
8:

> UPDATE AgendaRelaties SET Linkednr = @linked_id  WHERE ID = @linked_id
 how can you have that value of ID = @linked_id when that value has been generated just above?

 
> What remains is the question how to execute this from within Delphi?
see here for example(s), using the adodb.tlb_command:
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_20807225.html

The customer can be in that crossref table multiple times (related to other main-records).

They only problem is that I need to retrieve the ID (autoinc) from the first record that is created.
Does anybody have a solution for that?

All you need to do is to write your query with an output variable.  See the basic format in the snippet. Basically, you can do the insert and get back the Identity value in order to go on to create teh FK's for other related tables.

INSERT INTO yourtable
(
 columnnames
)
OUTPUT whatever columns you need back, like the identity column
VALUES
(
 corresponding values
);
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

locking should be done by the database itself
not by the developer

you should start a transaction

change table 1
change table 2
change table 3

end transaction

if all this is to prevent holes in your column then put that idea aside
holes will happen, no matter how you program

you could use two columns, one a autogenerated id and the other your next id

@DelphiWizard,

Yes, but then it was suppost that the ID came from the main table. Which isn't available at that time.

If you have already inserted the main table's record and retrieved the Identity value (and, again, I would suggest using the OUTPUT technique rather than the SCOPE_IDENTITY() or, heaven forbid, @@IDENTITY), then you should have that value available to you as you are inserting the additional data in other tables.  

As Geert pointed out, if you are concerned about needing to ensure that all or none of the data is committed to the tables, then wrap your process in a Transaction rahter than trying to selectively lock tables yourself.

There was a recent article about there being a bug in the SCOPE_IDENTITY() function which can under certain circumstances return the wrong value.  The OUTPUT technique does NOT suffer from this problem.

The customer can be in that crossref table multiple times (related to other main-records).

This shouldn't be a problem as long as you have the unique index set up so that it includes the main record ID and the customer ID (although, I would probably set up 2 unique indexes, one with main record then customer IDS and the other with Customer then main record IDs if you will be searching by either combination or just the main record or customer ID).

Hi there,

Will the following code work or can anybody make some adjustments to it?
And will it always return the correct ID or is it still possible to get the wrong one?

// StoredProcedure in SQL Server:
ALTER PROCEDURE [dbo].[AgendaRelatiesAdd] (
          @EigenBedrijfnr int,
          @Relatienr int,
          @Factureren bit
	 OUTPUT)
AS
BEGIN
    declare @ReturnID int
    set @ReturnID = 0
    
	INSERT INTO dbo.AgendaRelaties (EIGENBEDRIJFNR, RELATIENR, FACTUREREN)
	VALUES (@EigenBedrijfnr, @Relatienr, @Factureren)
	SET @ReturnID = SCOPE_IDENTITY() --nieuwe recordnummer teruggeven aan delphi-applicatie
 
Return @ReturnID
END
 
// In Delphi DataModule
procedure TDM.AgendaRelatiesAdd(vRelatienr : Integer; vFactureren : Boolean);
begin
  Screen.Cursor := crHourglass;
  try
    with aspAgendaRelatiesAdd do
    begin
      Close;
      Parameters.ParamByName('@EIGENBEDRIJFNR').Value := vEigenBedrijfnr;
      Parameters.ParamByName('@Relatienr').Value      := vRelatienr;
      Parameters.ParamByName('@Factureren').Value     := vFactureren;
      Prepared := True;
      Open;
    end;
  finally
    CheckReturn(aspAgendaRelatiesAdd);
  end;
end;
 
procedure TDM.CheckReturn(SP: TADOStoredProc);
var TerugWaarde: Variant;
begin
  try
    TerugWaarde := SP.Parameters.ParamByName('@RETURN_VALUE').Value;
  except
    TerugWaarde := 0;
  end;
  if ((VarIsNull(TerugWaarde) = False) and (VarIsNumeric(TerugWaarde) = True)) then
  begin
    if (TerugWaarde = 0) then // Error occured
    begin
      // Log Error
    end else
    begin
      // New recordID
      NewRecordID := TerugWaarde;
    end;
  end;
  Screen.Cursor := crDefault;
end;
 
Somewhere in my application:
DM.AgendaRelatiesAdd(QAgendaRelatiesSelecteren.FieldByName('Relatienr').AsInteger, QAgendaRelatiesSelecteren.FieldByName('Factureren').AsBoolean);

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:

yes, that looks like it should work (though I would not use the "return value", but a output parameter of the stored procedure ...

808 Diver:
Just two questions.

1. How certain is it that when two users run the stored procedure, that each user gets it's own Output back?
2. I guess it's assumed that the 2nd "TerugWaarde" in "OUTPUT @TerugWaarde = TerugWaarde" is the ID (autoinc) field of the table.

By definition, using the OUTPUT technique gives you the Identity that was just created for the record that was just inserted . . . kind of like a little built in trigger that hands it back to you. ;-)

Two people executing the SP will each a) insert their own data which will b) have its own Identity value which will then be c) returned to the respective SP executions.

Pretty cool, huh? ;-)

...and when  5 users run the stored procedure at the same time?

senad:
Good question.

8080 driver:
I assume that the number of users doesn't influence this behaviour, otherwise it's of no use. Would be nice to get that confirmed by you though.

I can confirm that scope_identity() is multi-user safe!

The thing is that the value is passed through the OUTPUT.
And the returned value will be gathered from within Delphi.
Are there than still no issues regarding multi-user and time of execution of the Delphi procedure TDM.CheckReturn(SP: TADOStoredProc);?

Thanks to you all.

When I try to alter the StoredProcedure I get following error:

"Msg 102, Level 15, State 1, Procedure AgendaRelatiesAdd, Line 17
Incorrect syntax near '='."

ID = the autoinc field of the table.

ALTER PROCEDURE [dbo].[AgendaRelatiesAdd] (
          @EigenBedrijfnr int,
          @Relatienr int,
          @Factureren bit OUTPUT,
          @TerugWaarde INT)
AS
BEGIN
    declare @ReturnID int
    set @ReturnID = 0
    
	INSERT INTO dbo.AgendaRelaties (EIGENBEDRIJFNR, RELATIENR, FACTUREREN)
         OUTPUT @TerugWaarde = ID 
	VALUES (@EigenBedrijfnr, @Relatienr, @Factureren)
 
Return @ReturnID
END

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Another thing is that the ReturnID that is declared is never filled with the ID of the autoinc-field.
So what is returned here?

this should work:

ALTER PROCEDURE [dbo].[AgendaRelatiesAdd] (
          @EigenBedrijfnr int,
          @Relatienr int,
          @Factureren bit OUTPUT,
          @TerugWaarde INT OUTPUT )
AS
BEGIN
    --declare @ReturnID int
    -- set @ReturnID = 0
    
	INSERT INTO dbo.AgendaRelaties (EIGENBEDRIJFNR, RELATIENR, FACTUREREN)
	VALUES (@EigenBedrijfnr, @Relatienr, @Factureren)
 
         SET @TerugWaarde = SCOPE_IDENTITY()
 
   -- Return @ReturnID
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:

Is the additional OUTPUT needed behind @Factureren?

         @Factureren bit OUTPUT,
         @TerugWaarde INT OUTPUT )

And in addition:
procedure TDM.CheckReturn(SP: TADOStoredProc); does give me the autoinc value, but after that I get following error.

aspAgendaRelatiesAdd: CommandText does not return a result set.

>Is the additional OUTPUT needed behind @Factureren?
no

>procedure TDM.CheckReturn(SP: TADOStoredProc); does give me the autoinc value, but after that I get following error.
>aspAgendaRelatiesAdd: CommandText does not return a result set.
sorry, but I don't know how/why that works in Delphi...

@angelIII,

I can confirm that scope_identity() is multi-user safe!

There was a recent article with confirmation from Microsoft that under certain circumstances both the @@Identity and the SCOPE_IDENTITY can return the wrong value in SQL Server 200 and 2005.  Supposedly the problem is fixed in SQL Server 2008.

no, because the session that generated the identity will pass exactly that value via scope_identity to the variable.
as from there, it will go through the output to the calling code, it cannot go to any other session.

That was the "official party line" until recently when it was discovered that there are, indeed, circumstances where that design fails.  However, Microsoft has not made a big deal out of it (other than to say, "Yes, we have confirmed it but we told people to use the OUTPUT approach.  Anyway, it's fixed in SS2008") mostly, I suspect, because they believe it is now fixed in SS2008 and they are moving on to SS2010.

@Delphiwizard,

I assume that the number of users doesn't influence this behaviour, otherwise it's of no use. Would be nice to get that confirmed by you though.

The number of users doesn't impact the OUTPUT technique.  As for confirmation, I am looking for the article I mentioned in the above point to angelIII.  However, since there is, in effect, no delay between the insert and the obtaining of the Identity vlaue when using the OUTPUT technique, unlike the execution of the INSERT statement and then executing a statement to set a variable to the SCOPE_IIDENTITY value (which, admittedly is a very small delay), the danger should be intuitively less.  However, the particular circumstances under which the SCOPE_IDENTITY failed (which Microsoft was actually able to reproduce) did not involve more than 2 or 3 processes wexecuting at the same time.

All I am saying is that @@IDENTITY was known to have some issues and that is why SCOPE_IDENTITY came about but SCOPE_IDENTITY (in SS2000 and 2005) is now also known to have issues (although, admittedly, not nearly to the extent that @@IDENTITY does ;-).

DelphiWizard,

>>aspAgendaRelatiesAdd: CommandText does not return a result set.
>sorry, but I don't know how/why that works in Delphi...

You have to assign the value of the returned parameter to a variable in Delphi, kind of like how you assigned the value of a variable to the parameters in order to call the SP except swapping the two two sides of the assignment. ;-)

8080 diver:

You have to assign the value of the returned parameter to a variable in Delphi, kind of like how you assigned the value of a variable to the parameters in order to call the SP except swapping the two two sides of the assignment. ;-)

I would very much appreciate it if you would give my an example for:

1. Correct StoredProcedure using the OUTPUT-way. Returning the ID-field of table "AgendaRelaties".
2. How to handle this in Delphi?

I would very much appreciate it if you would give my an example for:
1. Correct StoredProcedure using the OUTPUT-way. Returning the ID-field of table "AgendaRelaties".
2. How to handle this in Delphi?

Well, I have already posted a description of the answer to #2 and, as to #1, I posted my previous response with that example in a hurry, so I think the following is probably going to work better.  (Although, given that you have already assigned the points, you do realize that having left part of the problem as "an exercise for the student" is not to be unexpected. ;-)  

By the way, I tend to prefer using the RETURN value to indicate an error. ;-)

ALTER PROCEDURE [dbo].[AgendaRelatiesAdd] (
          @EigenBedrijfnr int,
          @Relatienr int,
          @Factureren bit OUTPUT,
          @TerugWaarde INT OUTPUT)
AS
BEGIN
--    declare @ReturnID int
--    set @ReturnID = 0
    
	INSERT INTO dbo.AgendaRelaties (EIGENBEDRIJFNR, RELATIENR, FACTUREREN)
         OUTPUT ID INTO @TerugWaarde	VALUES (@EigenBedrijfnr, @Relatienr, @Factureren)
 
--Return @ReturnID
RETURN @@ERROR
END
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Your code gives following errormessage when executed:

Msg 1087, Level 15, State 2, Procedure AgendaRelatiesAdd, Line 15
Must declare the table variable "@TerugWaarde".