May 17, 2010 at 7:50 am
I have a function that is supposed to get the next maximum value (plus 1) from a table. Here is the T-SQL:
ALTER FUNCTION [dbo].[udfDtsGetNewTraceID]
(
)
RETURNS INT
AS
BEGIN
DECLARE @intNewTraceID INT
SELECT @intNewTraceID =ISNULL(MAX(intDTSTrace_ID), 0) + 1
FROM tblDTSTrace
RETURN @intNewTraceID
END
There are thousands of records in this table with actual values in the field intDTSTrace_ID. Yet, this function sometimes returns zero. Can anyone explain why?
Thanks in advance,
Ray
May 17, 2010 at 8:03 am
post the DDL with some sample data. I am sure you can get HELP from here.
karthik
May 17, 2010 at 8:08 am
Just so I understand. . . run this and tell me if it gives you the result you'd expect.
DECLARE @testtable1 TABLE(
id INT IDENTITY(1, 1),
intdtstrace_id INT PRIMARY KEY CLUSTERED)
INSERT INTO @testtable1
(intdtstrace_id)
VALUES ('1')
INSERT INTO @testtable1
(intdtstrace_id)
VALUES ('2')
INSERT INTO @testtable1
(intdtstrace_id)
VALUES ('3')
INSERT INTO @testtable1
(intdtstrace_id)
VALUES ('4')
SELECT Isnull(MAX(intdtstrace_id), 0) + 1
FROM @testtable1
May 17, 2010 at 8:10 am
ray 78019 (5/17/2010)
I have a function that is supposed to get the next maximum value (plus 1) from a table. Here is the T-SQL:ALTER FUNCTION [dbo].[udfDtsGetNewTraceID]
(
)
RETURNS INT
AS
BEGIN
DECLARE @intNewTraceID INT
SELECT @intNewTraceID =ISNULL(MAX(intDTSTrace_ID), 0) + 1
FROM tblDTSTrace
RETURN @intNewTraceID
END
There are thousands of records in this table with actual values in the field intDTSTrace_ID. Yet, this function sometimes returns zero. Can anyone explain why?
Thanks in advance,
Ray
A return value zero doesn't seem possible, unless the max value is -1.
May 17, 2010 at 8:16 am
--EDIT-- The more I look at your code, the more it looks right. Just tested it on my server and got the expected answer. So I suspect, the error is in your data.
--EDIT 2-- I've tried lots of ways to replicate the '0' result and only come up with the previous mentioned "if the largest value is -1".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply