At 03:05 AM 3/05/2010, you wrote:

>Hi,
>
>I'm running FB 2.5 beta 2 and I'm porting a sql server db over to FB.
>
>I'm having trouble converting the following stored proc which essentially selects a row from a table of values (sequences in a single table) and returns the next value after incrementing it by 1.
>
>e.g
>SOME_ID 567
>THIS_VALUE 10
>THAT_VALUE 21
>
>Executing usp_GetNewValue('SOME_ID') would increment that row in the table to 568 and return 568.
>
>Could anyone convert this for me please?

Well, yes, someone could, but why don't you learn Firebird's procedural SQL language (PSQL) yourself? MSSQL Server is the only dbms that uses VBScript for stored procedures...

>create procedure usp_GetNewValue
> @SeqName nvarchar(30)
>as
>begin
> declare @NewSeqVal int
> set NOCOUNT ON
> update AUTONO
> set @NewSeqVal = ITEMVALUE = ITEMVALUE + 1
> where ITEM = @SeqName
>
> if @@rowcount = 0 begin
> return -1
> end
>
> return @NewSeqVal
>end

As a one-time guideline to your other conversions (please don't expect someone to do your conversions for you unless you are prepared to pay!):

create procedure usp_GetNewValue (
SeqName varchar(30))
returns (
NewSeqVal integer)
as
begin
select (ITEMVALUE + 1) from AUTONO
where ITEM = :SeqName
into :NewSeqVal;
/* Allow for null */
if (NewSeqVal is null) then
NewSeqVal = 1;

update AUTONO
set NewSeqVal = :NewSeqVal
where ITEM = :SeqName; /* Not sure that the field you really meant to update here wasn't ITEMVALUE, though) */
suspend;
end

Also read up about generators (sequences). They are more efficient for getting the value of an incrementing integer/BigInt series and are not prone to multi-user conflicts. The function GEN_ID(GeneratorName, step) returns the next value of the named generator, while the step specifies the difference between the previously generated value and the one you want.

HB