How to get the next value in a sequence

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 2008 Forums

Transact-SQL (2008)

How to get the next value in a sequence

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-04 : 09:36:05

Good morning.

I know this is a well used question. I can find variances of what I need but not exactly what I need, I have a feed to PeopleSoft which expects the next number in the sequence on each feed so I have a little table which saves the sequence number from last time and then I need to go into it and grab that one and start counting. I created a little function to go get the number and then I add one to it but it doesn't increment. So I have this:


create FUNCTION [dbo].[fn_GetNextFeedSeq] (@feed varchar(10))
RETURNS varchar(30)
AS
BEGIN
RETURN(

	select keenevalue
	from PSoftCodes
	where CodeName like @feed+'%intf_seq'
)
END

the number being referenced is 015611 so when I do a select in using the function on three rows I want 015612,015613,015614 but what I get is 015612,015612, 015612


CAST(dbo.fn_GetNextFeedSeq('AP')+1  AS varchar) VOUCHER_NUMBER,

Any ideas or pointers in the right direction would be great.

Thanks

Laura

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-04 : 09:39:18

[code]
CAST(dbo.fn_GetNextFeedSeq('AP') + row_number() over(order by somecol) AS varchar) VOUCHER_NUMBER
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-04 : 09:53:03

Magic!!!! Thanks so much.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources