February 22, 2006 at 6:55 am
Hi,
I have a table "tblDocument" with DocumentID as Primary Key. I have another table "tblRevision" with DocumentID as foreign key. It also contains a field called RevNo. If a Document is "issued", it gets inserted into tblRevision and the RevNo is indexed by 1.
I want to write a Scalar Function (which I will use in a few different places)to calculate what the next RevNo will be given the DocumentID but I can't get it to work. Here's what I've tried:
ALTER FUNCTION dbo.NextRevisionNo
(
@DocumentID int
)
RETURNS int
AS
BEGIN
Declare @RetVal int
SELECT @RetVal= SELECT MAX(RevNo) + 1 AS NextRev
FROM dbo.tblRevision
GROUP BY DocumentID
HAVING (DocumentID = @DocumentID)
RETURN @RetVal
END
but it doesn't work. I also need to handle the case where the DocumentID doesn't exist in tblRevision (i.e. it has never been issued). What I proposed to do fro that was to change my SELECT Max() bit to:
SELECT MAX(ISNULL(RevNo,-1)) + 1 AS NextRev
which "should" give 0 as NextRev.
Any help appreciated!
February 22, 2006 at 7:43 am
Hi dec
At first glance I'd say drop the extra 'Select' remove the 'As NextRev'
SELECT @RetVal= MAX(RevNo) + 1
FROM dbo.tblRevision
GROUP BY DocumentID
HAVING (DocumentID = @DocumentID)
And give that a try.
To account for Nulls use MAX(IsNull(RevNo,0)) + 1
February 22, 2006 at 8:14 am
Hi,
I got it to work by putting brackets around the SELECT statement i.e.
SELECT @RetVal= (SELECT MAX(RevNo)+1
FROM dbo.tblRevision
GROUP BY DocumentID
HAVING (DocumentID = @DocumentID))
Thanks for the help anyway.
February 22, 2006 at 11:07 am
Why bother with the GROUP BY and HAVING? A simple WHERE meets your needs here.
SELECT @RetVal = MAX(RevNo) + 1 FROM dbo.tblRevision WHERE DocumentID = @DocumentID
Eddie Wuerch
MCM: SQL
February 22, 2006 at 1:44 pm
--Your IsNull is in the wrong spot
CREATE FUNCTION dbo.CurrentRevisionNo
(
@DocumentID int
)
RETURNS int
AS
BEGIN
RETURN
(
SELECT ISNULL(MAX(RevNo),0) AS CurrentRev
FROM dbo.tblRevision
WHERE (DocumentID = @DocumentID)
)
END
GO
SELECT
DocumentID,
dbo.CurrentRevisionNo(DocumentID) CurrentRevNo,
dbo.CurrentRevisionNo(DocumentID) + 1 AS NextRevNo
FROM tblDocuments
February 23, 2006 at 1:43 am
Yep,
All sorted now.
Thanks for all of the help.
Dec.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply