August 1, 2005 at 8:32 pm
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.
August 1, 2005 at 9:40 pm
/* 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy