Prev: Header in Job Step Output file
Next: Get column value in delete trigger
From: fniles on 17 Jun 2010 16:34
Thank you.

With OUTPUT codes that you supplied, if more than 1 process call the stored
procedure at the same time, it will return the correct SecurityID, correct
?

"J.B. Moreno" <planB(a)newsreaders.com> wrote in message
news:170620100823218963%planB(a)newsreaders.com...
> In article <Xns9D9A91D9E41Yazorman(a)127.0.0.1>, Erland Sommarskog
> <esquel(a)sommarskog.se> wrote:
>
>> fniles (fniles(a)pfmail.com) writes:
>> > We are using VS 2008 and SQL Server 2005.
>> > I have a table Spread that has 1 Integer column called SecurityID.
>> >
>> > I have a stored procedure spUpdateSpread that increment the SecurityID
>> > by 1, and returns that value.
>> > If more than 1 process call the stored procedure at the same time, will
>> > it
>> > return the correct SecurityID ?
>> >...
>> > CREATE PROCEDURE [dbo].[spUpdateSpread]
>> > @SecurityID int output
>> > as
>> > update Spread set SecurityID = SecurityID + 1
>> > select @SecurityID = SecurityID from spread
>>
>> This is not safe, you would need to wrap it in a transaction:
>>
>> BEGIN TRANSACTION
>> update Spread set SecurityID = SecurityID + 1
>> select @SecurityID = SecurityID from spread
>> COMMIT TRANSACTION
>>
>> I've also seen it suggested that you can do:
>>
>> update Spread set @SecurityID = SecurityID = SecurityID + 1
>>
>> But this syntax is obscure in my eyes, and to me it seems to be
>> implementation-dependent.
>
> Instead of either of the above, I'd suggest using OUTPUT - a bit more
> typing, but straightforward.
>
> DECLARE @Output TABLE (securityID int);
>
> UPDATE SPREAD
> SET SecurityID = SecurityID + 1
> OUPUT INSERTED.SecurityID INTO @Output
>
> select SecurityID from @Output
>
> --
> J.B. Moreno


From: Erland Sommarskog on 17 Jun 2010 17:42
fniles (fniles(a)pfmail.com) writes:
> With OUTPUT codes that you supplied, if more than 1 process call the
> stored procedure at the same time, it will return the correct
> SecurityID, correct
> ?

Yes, the method suggested by John should be safe, although personally
I find it a little clumsy, with the addition of an extra table.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 17 Jun 2010 17:44
fniles (fniles(a)pfmail.com) writes:
> I also have another table HistTradesOrig that has an identity column ID.
> When adding a record to it in the stored procedure
> INSERT_INTO_HistTradesOrig, I want to return the value of ID that was just
> added, and also set the ORDER column to be @ACCOUNT + '-' +
> convert(varchar(50),@ID).
> I got the value of ID by "select @ID = SCOPE_IDENTITY()" as shown below.
> If more than 1 process call the stored procedure at the same time, will it
> return the correct ID ?

Yes, scope_identity is guaranteed to return a value which no other
process gets.

However, beware that you may not get contiguous values. That is, if
one insert yields 18 as the id, but the transaction later fails,
18 will still be consumed and never be reused.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: J.B. Moreno on 18 Jun 2010 02:59
Erland Sommarskog <esquel(a)sommarskog.se> wrote:

> fniles (fniles(a)pfmail.com) writes:
> > With OUTPUT codes that you supplied, if more than 1 process call the
> > stored procedure at the same time, it will return the correct
> > SecurityID, correct
> > ?
>
> Yes, the method suggested by John should be safe, although personally
> I find it a little clumsy, with the addition of an extra table.

I find the extra table a bit clumsy too, but OUPUT requires a table...

But it's simple and easy to understand.

--
J.B. Moreno
First  |  Prev  | 
Pages: 1 2
Prev: Header in Job Step Output file
Next: Get column value in delete trigger