Simulating sequence objects for sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Simulating sequence objects for sql server

Author

Topic

AM
Starting Member

1 Post

Posted - 2007-04-23 : 13:15:08

Guys,

We are trying implement sequence objects in sql server.

http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx

from the above blog we are using option 2 for simulating sequences.

If you look at option 2 we have to create table and stored procedure for each sequence.

Is there a way I can create a function and pass the <tablename> and it executes
respective stored procedure??

CREATE FUNCTION GETNEXTVAL(@TABLENAME VARCHAR(20))
RETURN INT

BEGIN

Exec GetNewSeqVal_<tablename>

END

Thanks

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-23 : 18:09:02

Is there any specific reason why you want a function to do this job? Because you cannot call stored procedures from functions. Instead you can write a stored procedure which will take the table name and call the appropriate sp using dynamic sql.

CREATE PROC GETNEXTVAL(@TABLENAME VARCHAR(20))
@RETURNVAL INT OUTPUT

BEGIN

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SeqNoOutside int;

SET @SQLString = N'Exec @seqnoinside = GetNewSeqVal_' + @tablename;
SET @ParmDefinition = N'@seqnoinside int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @seqnoinside=@SeqNooutside OUTPUT;

SELECT @RETURNVAL = @SeqNooutside

END

Refer to http://msdn2.microsoft.com/en-us/library/ms188001.aspx to find out how to use sp_executesql and get an output value.

An easier approach would be to use simple IF ELSE blocks and call appropriate sps.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-23 : 19:53:21

also, you can avoid all this if you just use IDENTITY columns.

www.elsasoft.org

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-04-24 : 10:09:04

Thanks for your reply

How do I modify the below stored procedure to spit out the output value. Right now they give 'Command(s) completed successfully.' message

any suggestions/inputs would help.

---- STORED PROC 1

create procedure GetNewSeqVal_FACTS_CASE_ID_SEQ
as
begin

declare @NewSeqValue int
set NOCOUNT ON
insert into FACTS_CASE_ID_SEQ (fname) values ('smith')
set @NewSeqValue = scope_identity()
delete from FACTS_CASE_ID_SEQ WITH (READPAST)
return @NewSeqValue
end

---- STORED PROC 2

CREATE PROC GETNEXTVAL(@TABLENAME VARCHAR(20), @RETURNVAL INT OUTPUT)
as
BEGIN

DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SeqNoOutside int;

SET @SQLString = N'Exec @seqnoinside = GetNewSeqVal_' + @tablename;
SET @ParmDefinition = N'@seqnoinside int OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @seqnoinside=@SeqNooutside OUTPUT;

SELECT @RETURNVAL = @SeqNooutside

END

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-24 : 10:11:30

have a look here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82566

www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources