Can I use UDF for get the sequence number ??

  • Hello, Nice to Meet you ^^;;

    I am converting program for Oracle to MS-SQL

    And The Client's needs is the SQL query should be able to run in Oracle & MS-SQL.

    The biggest problem is get the oracle sequence.

    In oracle, sequence can be get by select query like this,

    ' SELECT seqnecename.nextval from .. '

    but in MS-SQL, there is no sequence but only identity can be used.

    And in our application, identity can not be used because the number should be modified to another type like 'YYYYMM + sequence number'

    So, I thought making UDF to get sequencial number from sequence table like this,

    ---------------------------------------------------------------------------------

    CREATE function GET_SEQUENCE

    (

    @v_sequencename varchar(30)

    )

    returns varchar(12)

    AS

    BEGIN

    DECLARE @sequence INT

    UPDATE RDS_SEQUENCE SET @sequence

    = ACNT_SEQ

    = ( SELECT ACNT_SEQ + 1 FROM RDS_SEQUENCE )

    RETURN @sequence

    END

    but update query was error, and, I can not wrap the Stored prodecure with User Defined Function..

    Is there any idea can get the sequencial number from select query?

    Thanks for your kind reply and sorry for my poor English.

  • /* Use SCOPE_IDENTITY() to get the identity value inserted in scope */

    SET NOCOUNT ON

    GO

    CREATE TABLE TEST

    (

    MyID INT IDENTITY,

    MyOtherCol VARCHAR(20),

    MyDesc  VARCHAR(20)

    )

    GO

    CREATE PROCEDURE Test_Insert

    (

    @pMyDesc VARCHAR(20)

    )

    AS

    SET NOCOUNT ON

    INSERT INTO TEST (MyOtherCol, MyDesc) VALUES ('', @pMyDesc)

    UPDATE TEST

    SET

     MyOtherCol = CONVERT(VARCHAR, GETDATE(), 112) + CONVERT(VARCHAR(10), SCOPE_IDENTITY())

    WHERE

     MyID = SCOPE_IDENTITY()

    GO

    Test_Insert 'AAAAA'

    GO

    Test_Insert 'BBBBB'

    GO

    Test_Insert 'CCCCC'

    GO

    Test_Insert 'DDDDD'

    GO

    Test_Insert 'EEEEE'

    GO

    SELECT * FROM TEST

    GO

    DROP TABLE TEST

    GO

    DROP PROC

    Test_Insert

    Regards,
    gova

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply