Next Sequence Num - SQL Server Forums

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

Transact-SQL (2000)

Next Sequence Num

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-26 : 11:46:53

Guys,

I have following scenario which I have to return next values when I pass the id of the table, no of next values.

id checknum
________________
1 600
2 300

getnextcheck(1, 5) should give next 5 values 601, 602, 603, 604, 605
getnextcheck(2, 3) should give next 3 values 301, 302, 303

Is this possible with a database function

Any suggestions/inputs would help

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-26 : 12:04:30

A WHILE loop maybe?

or

SELECT d.CheckNum + z.Number
FROM Table1 AS d
CROSS JOIN master..spt_values AS z
WHERE d.ID = @Param1 AND z.Type = 'p' AND z.Number BETWEEN 1 AND @Param2

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-26 : 21:54:49

Careful using sequence tables or BOOM!!! Hundreds of deadlocks. See the following...

[url]http://www.sqlservercentral.com/Forums/Topic436388-145-1.aspx[/url]

--Jeff Moden

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 01:29:13

or simply:-

CREATE FUNCTION getnextcheck
(
@ID int,
@Number int
)
RETURNS @Result TABLE 
(
Number int 
)
AS

WHILE @Number >0
BEGIN
 INSERT @Result 
 SELECT checknum+1 
 FROM Table WHERE id=@ID

 SET @Number=@Number-1
END

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-27 : 05:50:33

quote:
Originally posted by Jeff Moden

Careful using sequence tables or BOOM!!! Hundreds of deadlocks. See the following...

http://www.sqlservercentral.com/Forums/Topic436388-145-1.aspx

SELECT d.CheckNum + z.Number
FROM Table1 AS d WITH (ROWLOCK)
CROSS JOIN master..spt_values AS z WITH (NOLOCK)
WHERE d.ID = @Param1 AND z.Type = 'p' AND z.Number BETWEEN 1 AND @Param2

E 12°55'05.25"
N 56°04'39.16"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-27 : 07:08:47

That's kinda where I went in the URL I mentioned, Peter... except I used a Tally table instead of spt_Values to increase the possible number of checks.

The other thing is that this is a sequence table. I posted the only way I've ever seen to update the sequence table correctly without using an explicit transaction and without causing deadlocks on what turns out to be a real "hot spot" in the system.

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-27 : 07:23:54

quote:
Originally posted by Jeff Moden

Careful using sequence tables or BOOM!!! Hundreds of deadlocks. See the following...

[url]http://www.sqlservercentral.com/Forums/Topic436388-145-1.aspx[/url]

--Jeff Moden

Why do I get this error despite giving the username and password?

quote:

You are not authorized to view this page
You might not have permission to view this directory or page using the credentials you supplied.

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

If you believe you should be able to view this directory or page, please try to contact the Web site by using any e-mail address or phone number that may be listed on the https://www.sqlservercentral.com home page.

You can click Search to look for information on the Internet.

HTTP Error 403 - Forbidden
Internet Explorer

Madhivanan

Failing to plan is Planning to fail

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2007-12-27 : 08:07:28

(madhivanan,

I updated your post and replaced the CODE tag with a QUOTE tag so it would wrap better so I could read it.

-graz)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-27 : 08:17:57

quote:
Originally posted by graz

(madhivanan,

I updated your post and replaced the CODE tag with a QUOTE tag so it would wrap better so I could read it.

-graz)

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.

Thanks Graz

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-27 : 11:25:22

quote:
Why do I get this error despite giving the username and password?

Dunno... I'll take a look...

--Jeff Moden

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-27 : 11:26:20

Heh... the answer is "ya gotta be a member". It's free and they don't spam your email...

--Jeff Moden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-28 : 01:12:47

quote:
Originally posted by Jeff Moden

Heh... the answer is "ya gotta be a member". It's free and they don't spam your email...

--Jeff Moden

Well. It is working now

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-28 : 02:30:43

Perfect... and sorry, I forgot you were already a member, Madhivanan...

--Jeff Moden

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-12-28 : 10:56:48

I am getting the following error when I try to compile the function

"Msg 156, Level 15, State 1, Procedure getnextcheck, Line 7
Incorrect syntax near the keyword 'WHILE'."

Thanks

quote:

Originally posted by visakh16

or simply:-

CREATE FUNCTION getnextcheck
(
@ID int,
@Number int
)
RETURNS @Result TABLE 
(
Number int 
)
AS

WHILE @Number >0
BEGIN
 INSERT @Result 
 SELECT checknum+1 
 FROM Table WHERE id=@ID

 SET @Number=@Number-1
END

GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 11:06:08

you need to enclose the function code in a BEGIN/END block:


CREATE FUNCTION getnextcheck
(
@ID int,
@Number int
)
RETURNS @Result TABLE 
(
Number int 
)
AS
begin
	WHILE @Number >0
	BEGIN
	 INSERT @Result 
	 SELECT checknum+1 
	 FROM [Table] WHERE id=@ID
	
	 SET @Number=@Number-1
	END
        return
end
GO

EDIT:
you also need a "return" statement at the end.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 11:17:41

Looks like you've got a problem with that code though. Nothing is updating your [table].checknum value so all the @result.number values will be the same.

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-28 : 13:17:23

scelamko,

TG is correct and, like I said on the other forum... you can't do this with a function because you cannot tables external to the function... it's gotta be a stored proc...

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources