Promblem sa user defined function u MS SQL 2000

Promblem sa user defined function u MS SQL 2000 09.06.2004. u 06:24 · pre 272 meseca #1

Dakle imam funkciju koja stalno izbacuje sledecu gresku kada pokusam da je kreiram na serveru:
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

Potpis

All beer is good. Some beer is better.

Re: Promblem sa user defined function u MS SQL 2000 09.06.2004. u 15:37 · pre 272 meseca #2

ako te dobro razumem, ti hoces da vratis rezultat ovog upita:

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

funkcija koju si ti napisao radi bez problema kod mene.
samo bih prokomentarisala da ti je ovaj kursor ovde nepotreban. ne samo da je nepotreban, vec bi ti pri velikom broju rekorda bio mnogo spor.
mislim da je dovoljno da napises jednu inline funkciju, naprimer nesto ovako:

CREATE FUNCTION CON_fncCnStatusChangedForCCode
(
@CCode varchar(50)
)
RETURNS table
AS
RETURN
(
SELECT tblConsignment.consignmentid
FROM tblConsignment
INNER JOIN tblDocument ON tblConsignment.consignmentid =
tblDocument.documentid
WHERE tblConsignment.edi = -1
AND tblDocument.ccode = @CCode
)
GO

i zatim:

select consignmentid
from CON_fncCnStatusChangedForCCode('a')
ORDER BY consignmentid

ako te nisam dobro razumela, odgovori pa cu pogledati malo bolje.

Re: Promblem sa user defined function u MS SQL 2000 14.06.2004. u 08:37 · pre 272 meseca #3

Problem resen, stvar je bila u tome da su se pojavili neki nestandardni karakteri koji se u query analyser-u i visual studiu nisu videli, nego sam ih video tek kada sam query iskopirao u notepad. Obrisao ih i radi bez problema.

Slazem se da bi moglo drugacije, ali ternutno prebacujemo ceo sistem sa Informix-a na SQL Server, tako da je sve "as-is". Kursor je koriscen u Informixu, tako da je i ovde. Optimizacija dolazi posle.

Hvala na odgovoru.

Potpis

All beer is good. Some beer is better.

Forum kao aplikacija na telefonu

Srodne teme