I have the following sp (SqlServer 2000)

ALTER     procedure dkspAkcijeNaStavciServisa @Dst_Id as uId as

-- Creating the temp. table
create table #Akcije (
    Id decimal(15,0) 
,   Dst_Id decimal(15,0)
,   Vrijeme DateTime
,   OpisAkcije varchar(100)
,   Ojd_Id decimal(15,0)
,   Ojd_Sifra int
,   Ojd_Naziv varchar(100)
,   Ojd_SifraNaziv varchar(100)
,   Djl_Id decimal(15,0)
,   Djl_Sifra int
,   Djl_Naziv varchar(100)
,   Djl_SifraNaziv varchar(100)
,   TekstAkcije varchar(500)
,   Interno bit);

while(@Dst_Id is not null)
begin
    -- Insert into temp table
    insert into #Akcije
    SELECT  dst.Id
    ,   dst.Id
    ,   dst.VrijemeUnosa
    ,   'Unos opisa kvara'
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   dst.Opis
    ,   cast(0 as bit)
    from dktDokument_Stavke dst
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optDjelatnici djl on djl.Id = dkm.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    where dst.Id = @Dst_Id

    -- Insert into temp. table
    insert into #Akcije
    SELECT  sds.Id
    ,   sds.Dst_Id
    ,   sds.Vrijeme
    ,   'Promijena statusa'
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   '(' + cast(vds.Rbr as varchar) + ') ' + vds.Naziv
    ,   cast(0 as bit)
    from dktStavkaDokumenta_Statusi sds
    inner join dktVrstaDokumenta_Statusi vds on vds.Id = sds.Vds_Id
    inner join optDjelatnici djl on djl.Id = sds.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join dktDokument_Stavke dst on dst.Id = sds.Dst_Id
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    where sds.Dst_Id = @Dst_Id

    -- Insert into temp. table
    insert into #Akcije
    SELECT  sdn.Id
    ,   sdn.Dst_Id
    ,   sdn.Vrijeme
    ,   'Dodavanje napomene'
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   sdn.Tekst
    ,   sdn.Interna
    from dktStavkaDokumenta_Napomene sdn
    inner join optDjelatnici djl on djl.Id = sdn.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join dktDokument_Stavke dst on dst.Id = sdn.Dst_Id
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    where sdn.Dst_Id = @Dst_Id

    -- Dohvati slanje robe u vanjski ili centralni servis
    insert into #Akcije
    SELECT  dst.Id
    ,   dst.Dst_Id
    ,   dst.VrijemeUnosa
    ,   case when(dkm.Ojd_Id2 is null) then 'Slanje stavke u vanjski servis' else 'Slanje stavke u centralni servis' end
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   case when(dkm.Ojd_Id2 is null)  then 'Vanjski servis: ' + osb_za_prt.Naziv
                        else 'Centralni servis: ' + osb_za_ojd2.Naziv end
    ,   cast(0 as bit)
    from dktDokument_Stavke dst
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optDjelatnici djl on djl.Id = dkm.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    left join optOrganizacijskeJedinice ojd2 on ojd2.Id = dkm.Ojd_Id2
    left join optOsobe osb_za_ojd2 on osb_za_ojd2.Id = ojd2.Osb_Id
    left join optPartneri prt on prt.Id = dkm.Prt_Id
    left join optOsobe osb_za_prt on osb_za_prt.Id = prt.Osb_Id
    inner join dktVrsteDokumenata vdk on vdk.Id = dkm.Vdk_Id
    inner join dktDokument_Statusi dss on dss.Dkm_Id = dkm.Id
    inner join dktVrstaDokumenta_Statusi vds on vds.Id = dss.Vds_Id
    where vdk.Oznaka = 'SRPS'
    and dss.Trenutni = 1
    and vds.Oznaka = 'ZKL'
    and dst.Dst_Id = @Dst_Id

    -- Dohvati vracanja robe iz vanjskog ili centralnog servisa
    insert into #Akcije
    SELECT  dst.Id
    ,   dst.Dst_Id
    ,   dst.VrijemeUnosa
    ,   case when(dkm.Ojd_Id2 is null) then 'Vracanje stavke iz vanjskog servisa' else 'Vracanje stavke iz centralnog servisa' end
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   case when(dkm.Ojd_Id2 is null)  then 'Vanjski servis: ' + osb_za_prt.Naziv
                        else 'Centralni servis: ' + osb_za_ojd2.Naziv end
    ,   cast(0 as bit)
    from dktDokument_Stavke dst
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optDjelatnici djl on djl.Id = dkm.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    left join optOrganizacijskeJedinice ojd2 on ojd2.Id = dkm.Ojd_Id2
    left join optOsobe osb_za_ojd2 on osb_za_ojd2.Id = ojd2.Osb_Id
    left join optPartneri prt on prt.Id = dkm.Prt_Id
    left join optOsobe osb_za_prt on osb_za_prt.Id = prt.Osb_Id
    inner join dktVrsteDokumenata vdk on vdk.Id = dkm.Vdk_Id
    inner join dktDokument_Statusi dss on dss.Dkm_Id = dkm.Id
    inner join dktVrstaDokumenta_Statusi vds on vds.Id = dss.Vds_Id
    where vdk.Oznaka = 'VRIS'
    and dss.Trenutni = 1
    and vds.Oznaka = 'ZKL'
    and dst.Dst_Id = @Dst_Id

    -- Dohvati izlaz robe iz servisa
    insert into #Akcije
    SELECT  dst.Id
    ,   dst.Dst_Id
    ,   dst.VrijemeUnosa
    ,   case when(dkm.Ojd_Id2 is null) then 'Izlaz robe prema partneru (kupcu)' else 'Izlaz robe prema organizacijskoj jedinici' end
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   case when(dkm.Ojd_Id2 is null)  then 'Partner (kupac): ' + osb_za_prt.Naziv
                        else 'Organizacijska jedinica: ' + osb_za_ojd2.Naziv end
    ,   cast(0 as bit)
    from dktDokument_Stavke dst
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join optDjelatnici djl on djl.Id = dkm.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    left join optOrganizacijskeJedinice ojd2 on ojd2.Id = dkm.Ojd_Id2
    left join optOsobe osb_za_ojd2 on osb_za_ojd2.Id = ojd2.Osb_Id
    left join optPartneri prt on prt.Id = dkm.Prt_Id
    left join optOsobe osb_za_prt on osb_za_prt.Id = prt.Osb_Id
    inner join dktVrsteDokumenata vdk on vdk.Id = dkm.Vdk_Id
    inner join dktDokument_Statusi dss on dss.Dkm_Id = dkm.Id
    inner join dktVrstaDokumenta_Statusi vds on vds.Id = dss.Vds_Id
    where vdk.Oznaka = 'IRS'
    and dss.Trenutni = 1
    and vds.Oznaka = 'ZKL'
    and dst.Dst_Id = @Dst_Id

    -- Dodaj zamjene/ugradnje u izlaznu tablicu
    insert into #Akcije
    select  sdv.Id
    ,   case when dst1.Dst_Id is null 
            then dst1.Id 
            else dst1.Dst_Id 
            end
    ,   sdv.VrijemeUnosa
    ,   case when(vvs.Oznaka = 'UGR') then 'Ugradnja dijela' else 'Zamjena dijela' end
    ,   ojd.Id
    ,   ojd.Sifra
    ,   osb_za_ojd.Naziv
    ,   cast(ojd.Sifra as varchar) + ' - ' + osb_za_ojd.Naziv
    ,   djl.Id
    ,   djl.Sifra
    ,   osb_za_djl.Naziv
    ,   cast(djl.sifra as varchar) + ' - ' + osb_za_djl.Naziv
    ,   case when(vvs.Oznaka = 'UGR') then 'Ugradeni dio: "' + 
            case when (art2.Naziv is null)
            then dst2.NazivArtiklaOpisno
            else art2.Naziv end + '"    '
        else    'Artikl "' +
            case when (art1.Naziv is null)
            then dst1.NazivArtiklaOpisno
            else art1.Naziv end 
            + '" je zamijenjen sa "' + 
            case when (art2.Naziv is null)
            then dst2.NazivArtiklaOpisno
            else art2.Naziv end + '"'
        end
    ,   cast(0 as bit)
    from dktStavkeDokumenataVeze sdv
    inner join dktVrsteVezaStavki vvs on vvs.Id = sdv.Vvs_Id 
    inner join dktDokument_Stavke dst1 on dst1.Id = sdv.Dst_Id
    left join rmtArtikli art1 on art1.Id = dst1.Art_Id
    inner join dktDokumenti dkm1 on dkm1.Id = dst1.Dkm_Id
    inner join dktVrsteDokumenata vdk1 on vdk1.Id = dkm1.Vdk_Id
    inner join optDjelatnici djl on djl.Id = dkm1.Djl_Id
    inner join optOsobe osb_za_djl on osb_za_djl.Id = djl.Osb_Id
    inner join optOrganizacijskeJedinice ojd on ojd.Id = dkm1.Ojd_Id
    inner join optOsobe osb_za_ojd on osb_za_ojd.Id = ojd.Osb_Id
    inner join dktDokument_Stavke dst2 on dst2.Id = sdv.Dst_Id_Veza
    left join rmtArtikli art2 on art2.Id = dst2.Art_Id
    where vvs.Oznaka in ('UGR', 'ZMJ')
    and vdk1.Oznaka = 'URS'
    and (((dst1.Dst_Id is null) and (dst1.Id = @Dst_Id)) or ((dst1.Dst_Id is not null) and (dst1.Dst_Id  = @Dst_Id)))

    -- Ako postoji ulazna stavka koja je nastala slanjem trenutne stavke
    declare @Dst_Id_Temp decimal(15,0);
    set @Dst_Id_Temp = null;
    SELECT  @Dst_Id_Temp = dst.Id
    from dktDokument_Stavke dst
    inner join dktDokumenti dkm on dkm.Id = dst.Dkm_Id
    inner join dktVrsteDokumenata vdk on vdk.Id = dkm.Vdk_Id
    inner join dktDokument_Statusi dss on dss.Dkm_Id = dkm.Id
    inner join dktVrstaDokumenta_Statusi vds on vds.Id = dss.Vds_Id
    inner join dktStavkeDokumenataVeze sdv on sdv.Dst_Id_Veza = dst.Id
    inner join dktDokument_Stavke dst_slanje on dst_slanje.Id = sdv.Dst_Id
    inner join dktDokumenti dkm_slanje on dkm_slanje.Id = dst_slanje.Dkm_Id
    inner join dktVrsteDokumenata vdk_slanje on vdk_slanje.Id = dkm_slanje.Vdk_Id
    inner join dktDokument_Statusi dss_slanje on dss_slanje.Dkm_Id = dkm_slanje.Id
    inner join dktVrstaDokumenta_Statusi vds_slanje on vds_slanje.Id = dss_slanje.Vds_Id
    where vdk.Oznaka = 'URS'
    and dss.Trenutni = 1
    and vds.Oznaka = 'ZKL'
    and vdk_slanje.Oznaka = 'SRPS'
    and dss_slanje.Trenutni = 1
    and vds_slanje.Oznaka = 'ZKL'
    and dst_slanje.Dst_Id = @Dst_Id

    -- Osiguraj iduci prolaz kroz petlju ako je potrebno
    set @Dst_Id = @Dst_Id_Temp;
end

begin
-- Select from the temp. table - this is what i want sp. to return
select  Id          as StavkaId
,   Dst_Id          as StavkaDstId
,   Vrijeme         as Vrijeme
,   OpisAkcije      as OpisAkcije
,   Ojd_Id          as OrganizacijskeJediniceId
,   Ojd_Sifra       as OrganizacijskeJediniceSifra
,   Ojd_Naziv       as OrganizacijskeJediniceNaziv
,   Ojd_SifraNaziv      as OrganizacijskeJediniceSifraNaziv
,   Djl_Id          as DjelatniciId
,   Djl_Sifra       as DjelatniciSifra
,   Djl_Naziv       as DjelatniciNaziv
,   Djl_SifraNaziv      as DjelatniciSifraNaziv
,   TekstAkcije         as TekstAkcije
,   Interno         as Interno
from #Akcije
order by Vrijeme
end
drop table #Akcije

Although this procedure has a select statement at the end, it is recognized by LLBLGen as Action sp. I want it as Retrieval sp.

What should I do?

Find the sproc in Catalog Explorer, Expand the sproc node, expand the Amount of Resultsets, click the number, press F2 and change the number to zero

I have it under "Action stored procedures" and I want it as "Retrieval stored procedure"

Change the number to positive then.

Like Lethologica says, and change it to 1 or 2. 1 gives back a datatable, 2 gives back a dataset. These errors are thrown by SqlServer 2000.

You can also opt to manually specify the # of resultsets, by setting the preference 'SqlServerAutoDetermineSProcType' to false.

Thanks Lethologica for chiming in on this

I don't get it.... When I try to ad this sp, I can add it only as a Action stored procedure. And Action stored procedures don't have the "Ammount resultsets" property. So, how can I change it then ???

In the catalog explorer -> browse to the procedure by clicking open catalog -> schema -> stored procedures. THen click open the node of the stored procedure and you should see a node #0. Right-click it and select Change from the context menu. You then can change it to 1 or 2 or another value. (but 1 or 2 are sufficient).

After you've done that, you can add it as a retrieval procedure.

Thanks. And sorry for me beeing stupid. Didn't see the "Catalog explorer" part of the messages so I tried to find it in Project explorer.

Otis wrote:

In the catalog explorer -> browse to the procedure by clicking open catalog -> schema -> stored procedures. THen click open the node of the stored procedure and you should see a node #0. Right-click it and select Change from the context menu. You then can change it to 1 or 2 or another value. (but 1 or 2 are sufficient).

After you've done that, you can add it as a retrieval procedure.

Hi,

I'm using the version 2.0.0.0 Final project designer. Why am I unable to modify the number of resultsets? I have a context menu when I right click on the stored procedure name (Edit / Properties... and others), the Parameters under the stored procedure name (Open in editor...), but nothing at all when I right click on Number of resultsets. There was also mention of selecting the actual number and using F2, but that also does nothing. Nor does double-clicking the number.

This stored procedure is under Retrieval Stored Procedures and it currently shows "1" after the #.

Also, I may be missing this in the documentation, but changing this doesn't seem to be included in the documentation, and I needed to search the forums to find out how to do this.

Thanks in advance!

You are inspecting the SP in the Project Explorer, while you should use the Catalog Explorer usually located on the right side of the window

Walaa wrote:

You are inspecting the SP in the Project Explorer, while you should use the Catalog Explorer usually located on the right side of the window

Well of course now I feel doubly stupid since I now notice that someone just above my question posted how stupid they felt about looking at the project exporer and not the catalog explorer.

Aside from whatever technical reason this has to be in the catalog explorer, it would be great to have this documented in the section on editing stored procedure calls. And using similar wording to what you have above:

To change the number of resultsets returned by a stored procedure, you can modify the value in the Catalog Explorer (not the Project Explorer where you modify almost everything else).

Thanks again for a great product and the great support.