Select statements included within a function cannot return data to a client.
Imam jos dve funckcije jako slicne ovoj (output ima 2 parametra umesto jednog, to je manje vise jedina razlika), koje rade bez problema.
Postavicu code ovde pa neko nek pogleda ako ima vremena. Hvala unapred.
Code:
CREATE FUNCTION dbo.CON_fncCnStatusChangedForCCode
(
@CCode varchar(50)
)
RETURNS @Documents TABLE
(
DocumentID int
)
AS
BEGIN
-- look for CNs for specified ccode that need status change reported
-- Returns recordset of documentids.
-- 20/11/02 DMW Created (from spcnstatuschanged)
DECLARE @DocumentID int
-- initialise variables
SET @DocumentID = 0
-- select data
DECLARE CnStatusChangedForCCode_ReadCursor CURSOR FOR
SELECT tblConsignment.consignmentid
FROM tblConsignment
INNER JOIN tblDocument ON tblConsignment.consignmentid = tblDocument.documentid
WHERE tblConsignment.edi = -1
AND tblDocument.ccode = @CCode
ORDER BY tblConsignment.consignmentid
OPEN CnStatusChangedForCCode_ReadCursor
FETCH NEXT FROM CnStatusChangedForCCode_ReadCursor
INTO @DocumentID
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO @Documents
(DocumentID)
VALUES
(@DocumentID)
FETCH NEXT FROM CnStatusChangedForCCode_ReadCursor
INTO @DocumentID
END
CLOSE CnStatusChangedForCCode_ReadCursor
DEALLOCATE CnStatusChangedForCCode_ReadCursor
RETURN
END
CREATE FUNCTION dbo.CON_fncCnStatusChangedForCCode
(
@CCode varchar(50)
)
RETURNS @Documents TABLE
(
DocumentID int
)
AS
BEGIN
-- look for CNs for specified ccode that need status change reported
-- Returns recordset of documentids.
-- 20/11/02 DMW Created (from spcnstatuschanged)
DECLARE @DocumentID int
-- initialise variables
SET @DocumentID = 0
-- select data
DECLARE CnStatusChangedForCCode_ReadCursor CURSOR FOR
SELECT tblConsignment.consignmentid
FROM tblConsignment
INNER JOIN tblDocument ON tblConsignment.consignmentid = tblDocument.documentid
WHERE tblConsignment.edi = -1
AND tblDocument.ccode = @CCode
ORDER BY tblConsignment.consignmentid
OPEN CnStatusChangedForCCode_ReadCursor
FETCH NEXT FROM CnStatusChangedForCCode_ReadCursor
INTO @DocumentID
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO @Documents
(DocumentID)
VALUES
(@DocumentID)
FETCH NEXT FROM CnStatusChangedForCCode_ReadCursor
INTO @DocumentID
END
CLOSE CnStatusChangedForCCode_ReadCursor
DEALLOCATE CnStatusChangedForCCode_ReadCursor
RETURN
END
All beer is good. Some beer is better.