SP kalder SP - hvordan hentes resultatet?

Jeg tror at du skal bruge et output argument.

Se f.eks.:
  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp

Du kan også bruge en global temporær tabel - ala dette

create procedure test2
as
select * into ##temp from mytable
go

create procedure test1
as
exec test2
select * from ##temptabel
go

Du bør naturligvis lige sikre dig mht om temptabellen findes - nedlægge den etc. Bemærk også at performance daler hvis du blander DDL og DML udtryk - de bør holdes adskildte for at sænke antal rekompileringer.

En anden mulighed er at benytte en userdefined function (pseudo-sql - check korrekt syntaks i Books Online)

create function test2
as
  return select something
go

create procedure test2
as
  select * from ::test1
go

og en tredje løsning er, at udnytte retur-variabler fra proceduren. Hvis data retur er en enkelt integer kan det gøres således

create procedure test2
as
  declare @r int
  select @r = id from table
  return @r
go

create procedure test1
as
  declare @e int
  exec @e = test2
go

Mvh
Troels

hmm... problemet er bare at "test2" også bliver kaldt fra ASP sider m.m. hvor det netop er den select der skal bruges...

Er det ikke muligt at få fat i "temptabel" fra test1 uden at skulle i gang med output etc...

----


Trer, den tredje løsning ser umidelbart bedst ud... men kan det ikke lade sig gøre at simpelt hen få fat i outputtet fra SELECT'en?

Hvis test2 fyrer en alm. select af og du i øvrigt ikke skal viderebehandle data i test1, så jo. Alle recordset genereret i en procedure returneres til din klient, du skal blot switche mellem dem i din asp-kode.

Men hvis du ønsker at behandle output fra en select i en anden stored procedure, så hænger du på en temp tabel, output variabel eller en funktion.

naa, problemet er at "test2" nogle gange vil blive kaldt direkte fra en ASP side, og andre gange fra en anden procedure...
De data som test2 fremkalder skal vises til klienten. I nogle tilfælde kommer dette så med et direkte kald fra klienten, andre gange skal det gemmes i en database... dette er hvad test1 skulle gøre.

Jeg vil rode lidt med de forskellige muligheder, og se hvad der virker... når jeg finder ud af det tildeler jeg points. Hvis ikke  der er andre der har forslag.

Hvorfor ikke så lave to sp'er og en fn?

create function fntest
as
  return select something
go

create procedure test2
as
  select * from ::fntest
go

create procedure test1
as
  select * from ::fntest
go

create procedure test2
as
  select * from ::test1
go


Så har du et ekstra lag der skulle løse dit problem.

det vil sige at alt havd jeg har i test2 nu smider jeg ind i fntest (med return i stedet)!?
Det er da en mulighed

Det var min tanke.  Eks. på funktion fra books online:

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
      FROM sales s, titles t
      WHERE s.stor_id = @storeid and
      t.title_id = s.title_id)
GO


Eneste er, at funktioner først er understøttet fra SQL Server 2000, så hvis du sidder på en 7'er...

jeg vil lige lade spørgsmålet hænge... det kunne jo være at der var nogen som sad inde med en metode hvor det kun er i test1 der skal ændres; så den stored procedure der kaldes ikke skal ændres, og alle andre filer der bruger den ikke skal tilrettes :)

Men tak for hjælpen :)

Hvad skal din test1 returnere?? En tabel eller en værdi? du siger ind i en variabel nemlig ;O)

janus>

Det svar lignede meget et spørgsmål !

:-)

haha ja... Først havde jeg nemlig en løsning, hvis det var en tabel den returnerede, men blev pludselig lidt i tvivl :O)

ja, den returnerer en tabel... men ikke en den findes i databasen.

Den returnerer med

SELECT mitfelt=@minvar

giver dette nogle muligheder?

ja der er flere muligheder!

Men for lige at være helt sikker.. hehe ... den sp når den returnerer en tabel vil det så sige at det faktisk bare er en enkel variabel som du laver en select på ??

Kunne du klare dig med at få en enkelt værdi retur fra den sp??

Så er det jo Arne_V's forslag med output variabel om igen.

create procedure test2 (@minvar varchar(2) output)
as
...

der kan bruges

jo, det er kun en variabel der skal hentes... denne variabel dannes dog i test2 ud fra et ton forskellige selects, updates etc...

Så jo, en enkelt værdi retur er hvad jeg skal ha'... men helst uden at ændre i selve test2.

Med det setup du har, så kan du ikke få en værdi retur fra sp test2 som du kan benytte i sp test1 uden at ændre i test2. Det er ikke muligt.

Fortsat - hvis du ændrer den oprindelige test2 til en funktion og opbygger en ny test2 (med det originale interface) som wrapper funktionen, så vil alle kald til test2 fortsætte som hidtil (fx fra .ASP siden).

I procedurer - dvs. test1 - hvor du skal tilgå test2's data skal du så blot kalde funktionen fremfor proceduren.

jojo det kan man nu godt :O)

Først skal du lige create et temp table som skal holde outputtet fra SP'en - noget ala:

create table #tab1 (
myvar varchar(20)
)

og så populere det med:
insert into #tab1 exec jk
og selecte
select * from #tab1

Hmm... det har du sådan set ret i (damn :-).

Eneste ulempe ved løsningen ovenfor er, at den procedure du create temp tabellen i bliver rekompileret ved hvert kald. Det vil performe lidt bedre, hvis man opretter en permanent tabel og indsætter / sletter i den i stedet.

Nejda slet ikke!! Det er jo kun en temporær tabel :O), det kræver mindre at oprette den og indsætte end at slette og indsætte i en permanent.

hvis 10 brugere kalder den funktion samtidig, vil dette så give et problem? Vil de forskellige tabeller så ikke overskrive hinanden?

Og hvornår bliver disse tabeller slettet igen?

Det giver ingen problemer, det er netop det smarte ved den slags #tabeller. De er netop udviklet lige akkurat til den slags temporære formål. Selve tabellen holdes kun i live sålænge connectionen lever og knytter sig følgeligt til den unikke connection, så det ikke giver nogle problemer ligegyldigt om der er 1 eller 100 brugere. Disse problemer vil kun ske med en permanent tabel, da denne jo er tilgængelig for alle connections !! (at det så også kan løses er nu en helt anden sniksnak *S*)

hvorfor råbte i allesammen ikke bare den løsning i kor med det samme?

Anyway... Jeg vil teste det senere, og give points når det virker ;)

tak!!

Janus -> Når man blander DDL og DML udtryk i en SP så vil SP'en blive rekompileret. Det koster, specielt hvis den kaldes tit. 

Dertil kommer, at når man indsætter i temporære objekter vælger SQL Server en mere aggressiv opdatering af statistikker end på permanente tabeller. Det betyder rent praktisk at query planen bliver regenereret og SP'en rekompileret for hvergang 6 rækker ændres i en temp tabel - mens det for en permanent tabel først sker ved ca. 20% ændrede rækker.

Fordelen ved den temporære tabel er så, at den med få rækker vil være i RAM fremfor på disk - og med et "sløvt" disksystem er der en del at at hente der.

Men lad os nu ikke blive for tekniske :-)

Ja det er korrekt at en rekompilering vil finde sted ved DDL og DML, men den med statistics den har jeg nu ikke lige hørt om sålænge der ikke oprettes index. Selve statistics oprettes under index creation, den statistic der bruges af optimizeren sker kun på enkelt kolonner og betyder egentlig ikke det store da der kun findes 1 værdi i den temp. tabel. Desuden vil optimizeren jo heller ikke lave statistics hvis selve execution planen koster mindre end selve statistics creation og/ eller hvis serveren har for travlt!

Det med 6 rækker i en temp. tabel har jeg heller ikke hørt om, har du en henvisning til læsestof :O)

Og velkommen Troels... Har ikke set dig her tidligere *S*

Tak Janus

Er egentlig kun gået ind her lidt for sjov... død periode på arbejde her midt i sommerferietiden :-(

mht opdatering af temp tabeller - du kan læse om autostats bl.a. på

http://support.microsoft.com:80/support/kb/articles/Q195/5/65.ASP&NoWebContent=1

Den kære Karen Delany har, så vidt jeg husker, også et par ord om det i nogle af sine bøger.

hmmm... nu sidder jeg og roder med den funktion som janus kom med:

create table #tab1 (
myvar varchar(20)
)
insert into #tab1 exec jk
select * from #tab1

Det giver mig bare et problem... hvis jeg fra selve enterprise manager kalder funktionen, virker alt som det skal - men hvis jeg kalder det med samme sætning etc... fra en ASP side kommer der ikke noget resultat tilbage!?
Kræves der en speciel form for rettigheder for at der kan executes på den måde???

Lav den i en sp !!

Var det ikke også det du ville fra starten ??

Både og Janus... ville egentligt gerne bruge den både så den kan hentes fra ASP og andre SP...

Men jeg vil prøve om det kan smides ind i en funktion i stedet... har aldrig brugt funktioner i SQL Server, så vil se om det hjælper :)

Points kommer når jeg finder ud af hvad for en løsning der i sidste ende bliver brugt :)


Det er vist det her problem jeg støder ind i:
http://support.microsoft.com/default.aspx?scid=kb;en-us;190589

ahh, jeg tror vi misforstår hinanden janus.

Din funktion er skam sat ind i en SP.
Jeg kalder så den SP med for eksempel
execute minsp minvar

hvis jeg gør dette igennem enterprise manager får jeg det rigtige resultat.
hvis jeg gør det igennem en ASP side returnerer den slet ikke noget; som om den crasher et eller andet sted inde i SPen :(

Hmm, lyder underligt.

Men prøv at gøre sådan, kald dette via en sp:

create table #tab1 (
myvar varchar(20)
)
insert into #tab1 exec jk

if exists(select * from #tab1)
select * from #tab1
else
select -1 as myvar

Den skulle gerne giver et eller andet retur :O), når du siger den ikke returnere noget, står den så og stener eller er det bare at tomt rs du får retur?? - Du har vel ikke en on error resume next på et eller andet sted i den asp-kode vel ;O)

Dette har jeg nu prøvet.
Satte SP'en jk til:

CREATE PROCEDURE jk AS

DECLARE @minvar nvarchar(4)
SET @minvar = 'test'
GO



samt


CREATE PROCEDURE jk2 AS

create table #tab1 (
myvar varchar(20)
)
insert into #tab1 exec jk

if exists(select * from #tab1)
select * from #tab1
else
select -1 as myvar

GO



Når jeg kalder
execute jk2
fra enterprise manager får jeg resultatet "-1" frem. Dette er ikke korrekt, men hva'.

Hvis jeg prøver fra ASP at køre det samme får jeg:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

test.asp, line 20


Hvilket er der hvor jeg skriver response.write("myvar"). Fejlen betyder at der ikke blev sendt et recordset - ikke kun at recordsettet er lukket, men at det ikke eksisterer!?
Hvis jeg havde executed en normalt SP ville det virke... men ikke når den går i flere lag.


Kan du ud fra det se hvad problemet er??


Jeg smider lidt flere points i puljen :)

SP'en jk skulle selvfølgelig være:

CREATE PROCEDURE jk AS
DECLARE @minvar nvarchar(4)
SET @minvar = 'test'
SELECT minvar = @minvar
GO

selv om dette ikke giver nogen forskel... hvis jeg kører kommandoen fra enterprise manager virker det, men ikke når jeg kører den fra ASP siden.
Hvis jeg fra ASP siden bare kører execute jk virker det dog...

jeg vil gerne kigge på det, men post lige din kode til oprettelse af conn, comm osv....

sådan her:

Set db = Server.CreateObject("ADODB.Connection")
db.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=***; PWD=***;Initial Catalog=***;Data Source=***;"

Set rs = db.execute("execute jk2")

response.write rs("myvar")

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


og som sagt, hvis jeg bare executer jk virker det - men jk2 giver ikke noget recordset :(

Håber du kan hjælpe.

har prøvet med functions nu hvor det andet ikke ser ud til at virke... men functions kan vist ikke lave deletes, insert og update statements :( så den løsning dur desværre ikke.

Jeg ville tro at det med de temporary tables ville være bedst - hvis bare jeg vidste hvordan det bedst kunne sættes op til at virke.

Hvis du lige har lidt tålmodighed, så skal jeg nok kigge på det :O)

lyder godt :) tak!

Hej Tuctoh, Nu er jeg ikke en haj til asp, men prøv lige således:

dim rs, db
set db = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
db.open "Provider=bla bla etc"
rs.open "execute jk2", db
response.write(rs("myvar"))

Mvh
Troels

det giver desværre samme problem trer :(

Det er fordi den returnere en print... ala den som du sikkert kender 1 rows affected osv..

Du skal bruge en set parameter : nocount on, sådan her:

CREATE PROCEDURE jk2 AS
set nocount on

create table #tab1 (
myvar varchar(20)
)
insert into #tab1 exec jk

if exists(select * from #tab1)
select * from #tab1
else
select -1 as myvar

genialt janus :)

tak for hjælpen!!

Noget andet er, den print som den ellers returnerer (1 rows...) kan man få ASP'en til at skrive den ud? Ikke fordi jeg skal bruge det, kunne bare være interessant.


Jeg giver de to andre 10 points også; deres svar var også gode, selv om dit var det jeg valgte :)

vil det egentligt give performance forbedring at sige set nocount off på alle mine stored procedures, når det alligevel ikke bruges, eller er det lige gyldigt?

Man kan godt få den message retur. Men kan ikke lige huske hvordan, da jeg ikke fandt det videre interessant haha...

Der vil være meget lidt performance gain ved at sætte nocount on i dine statements, faktisk nok ikke noget du vil kunen mærke!