Prev: Header in Job Step Output file
Next: Get column value in delete trigger
From: fniles on 16 Jun 2010 14:12
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 ?

Thank you

CREATE TABLE [dbo].[Spread](
SecurityID [int] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[SecurityID] ASC
)
) ON [PRIMARY]


CREATE PROCEDURE [dbo].[spUpdateSpread]
@SecurityID int output
as
update Spread set SecurityID = SecurityID + 1
select @SecurityID = SecurityID from spread


From: Bob Barrows on 16 Jun 2010 14:33
fniles wrote:
> 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 ?

Not if you do it like this:

update Spread set @SecurityID = SecurityID + 1,SecurityID = @SecurityID

--
HTH,
Bob Barrows


From: Erland Sommarskog on 16 Jun 2010 18:03
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.

--
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: fniles on 16 Jun 2010 22:24
Thank you, everybody.

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 ?

Thanks

CREATE PROCEDURE INSERT_INTO_HistTradesOrig
@ID int output,
@Order varchar(50) = NULL,
@ACCOUNT varchar(10) = NULL
AS
insert into HistTradesOrig
([Order])
values
(@Order)

select @ID = SCOPE_IDENTITY()
UPDATE HistTradesOrig
SET [Order] = @ACCOUNT + '-' + convert(varchar(50),@ID)
WHERE ID = @ID
GO

CREATE TABLE HistTradesOrig (
ID int IDENTITY(1,1),
[order] varchar(50) NOT NULL,
Account varchar(10) NULL
)
go

ALTER TABLE HistTradesOrig
ADD PRIMARY KEY ([order])


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D9A91D9E41Yazorman(a)127.0.0.1...
> 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.
>
> --
> 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 17 Jun 2010 12:23
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
 |  Next  |  Last
Pages: 1 2
Prev: Header in Job Step Output file
Next: Get column value in delete trigger