Get SQL Variable

[Lasse Edsvik]

Hello

I have this:

set conn=server.createobject("adodb.connection")
conn.open .....................
sql="exec insertshit @shit='blah'"
conn.execute(sql)

was wondering how to get the variable that the stored procedure resturns?

I cant change:

set conn=server.createobject("adodb.connection")
conn.open .....................


/Lasse

[Ray at]

Does your SP return values? Just do it the same way you'd do any other
query.

Set rs = conn.execute(sql)
value = rs.fields.item(0).value


Ray at work


"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...

> Hello
>
> I have this:
>
> set conn=server.createobject("adodb.connection")
> conn.open .....................
> sql="exec insertshit @shit='blah'"
> conn.execute(sql)
>
> was wondering how to get the variable that the stored procedure resturns?
>
> I cant change:
>
> set conn=server.createobject("adodb.connection")
> conn.open .....................
>
>
> /Lasse
>
>

[Lasse Edsvik]

Ray,


CREATE Procedure .........
@Shit varchar(10)
@ShitID int output
AS
BEGIN
SET NOCOUNT ON

INSERT INTO Shit(Shit)VALUES(@Shit)

SELECT @ShitID=SCOPE_IDENTITY()
END
GO



works but dont know how to retreive it in the asp page

/Lasse


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...

> Does your SP return values? Just do it the same way you'd do any other
> query.
>
> Set rs = conn.execute(sql)
> value = rs.fields.item(0).value
>
>
> Ray at work
>
>
> "Lasse Edsvik" <lasse@nospam.com> wrote in message
> news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...

> > Hello
> >
> > I have this:
> >
> > set conn=server.createobject("adodb.connection")
> > conn.open .....................
> > sql="exec insertshit @shit='blah'"
> > conn.execute(sql)
> >
> > was wondering how to get the variable that the stored procedure

resturns?

> >
> > I cant change:
> >
> > set conn=server.createobject("adodb.connection")
> > conn.open .....................
> >
> >
> > /Lasse
> >
> >

>
>

[Ray at]

> > Set rs = conn.execute(sql)

> > value = rs.fields.item(0).value

Ray at work

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:%23DLhEIw1DHA.4060@TK2MSFTNGP11.phx.gbl...

> Ray,
>
>
> CREATE Procedure .........
> @Shit varchar(10)
> @ShitID int output
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT INTO Shit(Shit)VALUES(@Shit)
>
> SELECT @ShitID=SCOPE_IDENTITY()
> END
> GO
>
>
>
> works but dont know how to retreive it in the asp page
>
> /Lasse
>
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...

> > Does your SP return values? Just do it the same way you'd do any other
> > query.
> >
> > Set rs = conn.execute(sql)
> > value = rs.fields.item(0).value
> >
> >
> > Ray at work
> >
> >
> > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...

> > > Hello
> > >
> > > I have this:
> > >
> > > set conn=server.createobject("adodb.connection")
> > > conn.open .....................
> > > sql="exec insertshit @shit='blah'"
> > > conn.execute(sql)
> > >
> > > was wondering how to get the variable that the stored procedure

> resturns?

> > >
> > > I cant change:
> > >
> > > set conn=server.createobject("adodb.connection")
> > > conn.open .....................
> > >
> > >
> > > /Lasse
> > >
> > >

> >
> >

>
>

[Lasse Edsvik]

Ray,

rs.fields.item(0).value

can i put name in instead of 0 so it makes more sense? :) or is it just some
kind of index?

/Lasse


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:O$jHEKw1DHA.2680@tk2msftngp13.phx.gbl...

> > > Set rs = conn.execute(sql)
> > > value = rs.fields.item(0).value

>
> Ray at work
>
> "Lasse Edsvik" <lasse@nospam.com> wrote in message
> news:%23DLhEIw1DHA.4060@TK2MSFTNGP11.phx.gbl...

> > Ray,
> >
> >
> > CREATE Procedure .........
> > @Shit varchar(10)
> > @ShitID int output
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > INSERT INTO Shit(Shit)VALUES(@Shit)
> >
> > SELECT @ShitID=SCOPE_IDENTITY()
> > END
> > GO
> >
> >
> >
> > works but dont know how to retreive it in the asp page
> >
> > /Lasse
> >
> >
> > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> > news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...

> > > Does your SP return values? Just do it the same way you'd do any

other

> > > query.
> > >
> > > Set rs = conn.execute(sql)
> > > value = rs.fields.item(0).value
> > >
> > >
> > > Ray at work
> > >
> > >
> > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...
> > > > Hello
> > > >
> > > > I have this:
> > > >
> > > > set conn=server.createobject("adodb.connection")
> > > > conn.open .....................
> > > > sql="exec insertshit @shit='blah'"
> > > > conn.execute(sql)
> > > >
> > > > was wondering how to get the variable that the stored procedure

> > resturns?

> > > >
> > > > I cant change:
> > > >
> > > > set conn=server.createobject("adodb.connection")
> > > > conn.open .....................
> > > >
> > > >
> > > > /Lasse
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Ray at]

Yeah, it's just an index. It'll make your code .000000000001 seconds
faster. You can name it though like so:

CREATE Procedure .........
@something varchar(10)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO theTable(theColumn)VALUES(@something)

SELECT SCOPE_IDENTITY() As theNewID
END
GO

Then use rs.fields.item("theNewID").value

Ray at work

"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:eAY%23wMw1DHA.2336@TK2MSFTNGP09.phx.gbl...

> Ray,
>
> rs.fields.item(0).value
>
> can i put name in instead of 0 so it makes more sense? :) or is it just

some

> kind of index?
>
> /Lasse
>
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:O$jHEKw1DHA.2680@tk2msftngp13.phx.gbl...

> > > > Set rs = conn.execute(sql)
> > > > value = rs.fields.item(0).value

> >
> > Ray at work
> >
> > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > news:%23DLhEIw1DHA.4060@TK2MSFTNGP11.phx.gbl...

> > > Ray,
> > >
> > >
> > > CREATE Procedure .........
> > > @Shit varchar(10)
> > > @ShitID int output
> > > AS
> > > BEGIN
> > > SET NOCOUNT ON
> > >
> > > INSERT INTO Shit(Shit)VALUES(@Shit)
> > >
> > > SELECT @ShitID=SCOPE_IDENTITY()
> > > END
> > > GO
> > >
> > >
> > >
> > > works but dont know how to retreive it in the asp page
> > >
> > > /Lasse
> > >
> > >
> > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

message

> > > news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...
> > > > Does your SP return values? Just do it the same way you'd do any

> other

> > > > query.
> > > >
> > > > Set rs = conn.execute(sql)
> > > > value = rs.fields.item(0).value
> > > >
> > > >
> > > > Ray at work
> > > >
> > > >
> > > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > > news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...
> > > > > Hello
> > > > >
> > > > > I have this:
> > > > >
> > > > > set conn=server.createobject("adodb.connection")
> > > > > conn.open .....................
> > > > > sql="exec insertshit @shit='blah'"
> > > > > conn.execute(sql)
> > > > >
> > > > > was wondering how to get the variable that the stored procedure
> > > resturns?
> > > > >
> > > > > I cant change:
> > > > >
> > > > > set conn=server.createobject("adodb.connection")
> > > > > conn.open .....................
> > > > >
> > > > >
> > > > > /Lasse
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Bob Barrows]

Lasse Edsvik wrote:

> Ray,
>
>
> CREATE Procedure .........
> @Shit varchar(10)
> @ShitID int output
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT INTO Shit(Shit)VALUES(@Shit)
>
> SELECT @ShitID=SCOPE_IDENTITY()
> END
> GO
>
>
>

To retrieve output parameters, you need to use a Command object. You COULD
switch to using a SELECT statement to return the value, but I consider this
to be very wasteful when only a single value needs to be returned.

Using Command objects can be tricky but:
1. To me it's wothwhile since they are very efficient, and,
2. There are code generators that create the code needed to instantiate and
execute the Command object. I wrote one which is normally available here:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url],
but at this time, the link to download it appears to be broken. I have
notified the webmaster, but, in the meantime, if you would like a copy,
email me offline.

Here is how I would run this procedure:
const adCmdStoredProc=&H0004
Const adExecuteNoRecords = &H00000080
Const adInteger = 3
Const adVarChar = 200
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamReturnValue = &H0004

Set cmd=server.createobject("adodb.command")
With cmd
.CommandText="Insertshit"
.CommandType= adCmdStoredProc
set .ActiveConnection = conn
set params=.Parameters
params.Append .CreateParameter("Return_value",adInteger, _
adParamReturnValue)
params.Append .CreateParameter("@Shit",adVarChar, _
adParamInput,10,"blah")
params.Append .CreateParameter("@ShitID",adInteger, _
adParamOutput)
.Execute ,,adExecuteNoRecords
ShitID = params("@ShitID")
End with

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lasse Edsvik]

hmm

having problem grandmasta Ray........

not sure why..... but....


sql="exec TourAdd @tour='"&tour&"'"
set rs=conn.execute(sql)
value = rs.fields.item(0).value

CREATE PROCEDURE TourAdd
@Tour varchar(10),
@TourNo int output
AS
BEGIN
SET NOCOUNT ON

INSERT INTO Tours(TourNo)
VALUES(@TourNo)

SELECT @TourNo=SCOPE_IDENTITY()
END
GO



why does it say:

Procedure 'TourAdd' expects parameter '@TourNo', which was not supplied.


when its an output? damn, i have this com-componet that i use to execute
stored procedures usually but i can use it now and it works with that.......


i want the value returned as a variable god dammit *sounds like cartman in
southpark* lol

/Lasse


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%23Z$f4bw1DHA.3468@TK2MSFTNGP11.phx.gbl...

> Yeah, it's just an index. It'll make your code .000000000001 seconds
> faster. You can name it though like so:
>
> CREATE Procedure .........
> @something varchar(10)
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT INTO theTable(theColumn)VALUES(@something)
>
> SELECT SCOPE_IDENTITY() As theNewID
> END
> GO
>
> Then use rs.fields.item("theNewID").value
>
> Ray at work
>
> "Lasse Edsvik" <lasse@nospam.com> wrote in message
> news:eAY%23wMw1DHA.2336@TK2MSFTNGP09.phx.gbl...

> > Ray,
> >
> > rs.fields.item(0).value
> >
> > can i put name in instead of 0 so it makes more sense? :) or is it just

> some

> > kind of index?
> >
> > /Lasse
> >
> >
> > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> > news:O$jHEKw1DHA.2680@tk2msftngp13.phx.gbl...

> > > > > Set rs = conn.execute(sql)
> > > > > value = rs.fields.item(0).value
> > >
> > > Ray at work
> > >
> > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > news:%23DLhEIw1DHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > Ray,
> > > >
> > > >
> > > > CREATE Procedure .........
> > > > @Shit varchar(10)
> > > > @ShitID int output
> > > > AS
> > > > BEGIN
> > > > SET NOCOUNT ON
> > > >
> > > > INSERT INTO Shit(Shit)VALUES(@Shit)
> > > >
> > > > SELECT @ShitID=SCOPE_IDENTITY()
> > > > END
> > > > GO
> > > >
> > > >
> > > >
> > > > works but dont know how to retreive it in the asp page
> > > >
> > > > /Lasse
> > > >
> > > >
> > > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

> message

> > > > news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...
> > > > > Does your SP return values? Just do it the same way you'd do any

> > other

> > > > > query.
> > > > >
> > > > > Set rs = conn.execute(sql)
> > > > > value = rs.fields.item(0).value
> > > > >
> > > > >
> > > > > Ray at work
> > > > >
> > > > >
> > > > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > > > news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...
> > > > > > Hello
> > > > > >
> > > > > > I have this:
> > > > > >
> > > > > > set conn=server.createobject("adodb.connection")
> > > > > > conn.open .....................
> > > > > > sql="exec insertshit @shit='blah'"
> > > > > > conn.execute(sql)
> > > > > >
> > > > > > was wondering how to get the variable that the stored procedure
> > > > resturns?
> > > > > >
> > > > > > I cant change:
> > > > > >
> > > > > > set conn=server.createobject("adodb.connection")
> > > > > > conn.open .....................
> > > > > >
> > > > > >
> > > > > > /Lasse
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Lasse Edsvik]

hmm, its an sql prob, i cant run proc in QA,

k, ive had like 8 beers so you gotta help me lol

I have a table

TourNo Identity(1,1)
Tourname char(10)

and proc is:

ALTER PROCEDURE TourAdd
@Tourname varchar(10),
@TourNo int output
AS
BEGIN
SET NOCOUNT ON

INSERT INTO Tours(Tourname)
VALUES(@Tourname)

SELECT @TourNo=SCOPE_IDENTITY()
END
GO



when i run:

EXECUTE TourAdd @Tourname='a'

i get:

Server: Msg 201, Level 16, State 4, Procedure TourAdd, Line 0
Procedure 'TourAdd' expects parameter '@TourNo', which was not supplied.


what else do i need to specify in EXECUTE thingy?
/Lasse


"Lasse Edsvik" <lasse@nospam.com> wrote in message
news:OZwHmtw1DHA.2636@TK2MSFTNGP09.phx.gbl...

> hmm
>
> having problem grandmasta Ray........
>
> not sure why..... but....
>
>
> sql="exec TourAdd @tour='"&tour&"'"
> set rs=conn.execute(sql)
> value = rs.fields.item(0).value
>
> CREATE PROCEDURE TourAdd
> @Tour varchar(10),
> @TourNo int output
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT INTO Tours(TourNo)
> VALUES(@TourNo)
>
> SELECT @TourNo=SCOPE_IDENTITY()
> END
> GO
>
>
>
> why does it say:
>
> Procedure 'TourAdd' expects parameter '@TourNo', which was not supplied.
>
>
> when its an output? damn, i have this com-componet that i use to execute
> stored procedures usually but i can use it now and it works with

that.......

>
>
> i want the value returned as a variable god dammit *sounds like cartman in
> southpark* lol
>
> /Lasse
>
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:%23Z$f4bw1DHA.3468@TK2MSFTNGP11.phx.gbl...

> > Yeah, it's just an index. It'll make your code .000000000001 seconds
> > faster. You can name it though like so:
> >
> > CREATE Procedure .........
> > @something varchar(10)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > INSERT INTO theTable(theColumn)VALUES(@something)
> >
> > SELECT SCOPE_IDENTITY() As theNewID
> > END
> > GO
> >
> > Then use rs.fields.item("theNewID").value
> >
> > Ray at work
> >
> > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > news:eAY%23wMw1DHA.2336@TK2MSFTNGP09.phx.gbl...

> > > Ray,
> > >
> > > rs.fields.item(0).value
> > >
> > > can i put name in instead of 0 so it makes more sense? :) or is it

just

> > some

> > > kind of index?
> > >
> > > /Lasse
> > >
> > >
> > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

message

> > > news:O$jHEKw1DHA.2680@tk2msftngp13.phx.gbl...
> > > > > > Set rs = conn.execute(sql)
> > > > > > value = rs.fields.item(0).value
> > > >
> > > > Ray at work
> > > >
> > > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > > news:%23DLhEIw1DHA.4060@TK2MSFTNGP11.phx.gbl...
> > > > > Ray,
> > > > >
> > > > >
> > > > > CREATE Procedure .........
> > > > > @Shit varchar(10)
> > > > > @ShitID int output
> > > > > AS
> > > > > BEGIN
> > > > > SET NOCOUNT ON
> > > > >
> > > > > INSERT INTO Shit(Shit)VALUES(@Shit)
> > > > >
> > > > > SELECT @ShitID=SCOPE_IDENTITY()
> > > > > END
> > > > > GO
> > > > >
> > > > >
> > > > >
> > > > > works but dont know how to retreive it in the asp page
> > > > >
> > > > > /Lasse
> > > > >
> > > > >
> > > > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

> > message

> > > > > news:%23rgDO$v1DHA.3468@TK2MSFTNGP11.phx.gbl...
> > > > > > Does your SP return values? Just do it the same way you'd do

any

> > > other
> > > > > > query.
> > > > > >
> > > > > > Set rs = conn.execute(sql)
> > > > > > value = rs.fields.item(0).value
> > > > > >
> > > > > >
> > > > > > Ray at work
> > > > > >
> > > > > >
> > > > > > "Lasse Edsvik" <lasse@nospam.com> wrote in message
> > > > > > news:eWG$L6v1DHA.560@TK2MSFTNGP11.phx.gbl...
> > > > > > > Hello
> > > > > > >
> > > > > > > I have this:
> > > > > > >
> > > > > > > set conn=server.createobject("adodb.connection")
> > > > > > > conn.open .....................
> > > > > > > sql="exec insertshit @shit='blah'"
> > > > > > > conn.execute(sql)
> > > > > > >
> > > > > > > was wondering how to get the variable that the stored

procedure

> > > > > resturns?
> > > > > > >
> > > > > > > I cant change:
> > > > > > >
> > > > > > > set conn=server.createobject("adodb.connection")
> > > > > > > conn.open .....................
> > > > > > >
> > > > > > >
> > > > > > > /Lasse
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Bob Barrows]

Lasse Edsvik wrote:

> hmm, its an sql prob, i cant run proc in QA,
>
> k, ive had like 8 beers so you gotta help me lol
>
> I have a table
>
> TourNo Identity(1,1)
> Tourname char(10)
>
> and proc is:
>
> ALTER PROCEDURE TourAdd
> @Tourname varchar(10),
> @TourNo int output
> AS
> BEGIN
> SET NOCOUNT ON
>
> INSERT INTO Tours(Tourname)
> VALUES(@Tourname)
>
> SELECT @TourNo=SCOPE_IDENTITY()
> END
> GO
>
>
>
> when i run:
>
> EXECUTE TourAdd @Tourname='a'
>
> i get:
>
> Server: Msg 201, Level 16, State 4, Procedure TourAdd, Line 0
> Procedure 'TourAdd' expects parameter '@TourNo', which was not
> supplied.
>
>
> what else do i need to specify in EXECUTE thingy?

You have not provided a default value for the output parameter, so it is
required. Do this in QA:

declare @newid int
set @newid=0
EXECUTE TourAdd 'a', @newid output
print 'The new id is ' + cast(@newid as varchar(10))

HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lasse Edsvik]

that worked....... sigh

but why doesnt:

sql="exec TourAdd @tourname='"&tour&"',@tourno int output"
set rs=conn.execute(sql)
value = rs.fields.item(0).value


work in code.... i get same error

/Lasse


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O2yz01w1DHA.2868@TK2MSFTNGP09.phx.gbl...

> Lasse Edsvik wrote:

> > hmm, its an sql prob, i cant run proc in QA,
> >
> > k, ive had like 8 beers so you gotta help me lol
> >
> > I have a table
> >
> > TourNo Identity(1,1)
> > Tourname char(10)
> >
> > and proc is:
> >
> > ALTER PROCEDURE TourAdd
> > @Tourname varchar(10),
> > @TourNo int output
> > AS
> > BEGIN
> > SET NOCOUNT ON
> >
> > INSERT INTO Tours(Tourname)
> > VALUES(@Tourname)
> >
> > SELECT @TourNo=SCOPE_IDENTITY()
> > END
> > GO
> >
> >
> >
> > when i run:
> >
> > EXECUTE TourAdd @Tourname='a'
> >
> > i get:
> >
> > Server: Msg 201, Level 16, State 4, Procedure TourAdd, Line 0
> > Procedure 'TourAdd' expects parameter '@TourNo', which was not
> > supplied.
> >
> >
> > what else do i need to specify in EXECUTE thingy?

>
> You have not provided a default value for the output parameter, so it is
> required. Do this in QA:
>
> declare @newid int
> set @newid=0
> EXECUTE TourAdd 'a', @newid output
> print 'The new id is ' + cast(@newid as varchar(10))
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

[Bob Barrows]

Lasse Edsvik wrote:

> that worked....... sigh
>
> but why doesnt:
>
> sql="exec TourAdd @tourname='"&tour&"',@tourno int output"
> set rs=conn.execute(sql)
> value = rs.fields.item(0).value
>
>

I told you in my previous message: You need to use a Command object to
retrieve output and return values. Your stored procedure is not returning a
resultset, so there will not be a recordset.

Just to clarify:
SELECT @var = somevalue
does not return a resultset: it assigns the value to the variable. Now you
could create a SELECT statement that returns that value, like this:
SELECT somevalue as value_returned_in_resultset

but, again, this is wasteful. It is better IMO to use the output parameter.

The basics:
There are three ways to return values from a procedure:
1. Use a SELECT statement to return a resultset, a set of data in tabular
format
2. Use the Return statement:
create procedure ReturnSomething AS
Return 25
go
execute ReturnSomething
or
declare @retval
exec @retval = ReturnSomething
Select @retval as value_returned_by_Return_statement

3. Use an output parameter
described in my previous reply

HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Lasse Edsvik]

Bob, Ray,

thank you guys for helping my sorry ass out. brain is like an old mushrroom
right now but i got it to work.

thanks guys for helping
/Lasse


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u3EUhBx1DHA.2580@TK2MSFTNGP09.phx.gbl...

> Lasse Edsvik wrote:

> > that worked....... sigh
> >
> > but why doesnt:
> >
> > sql="exec TourAdd @tourname='"&tour&"',@tourno int output"
> > set rs=conn.execute(sql)
> > value = rs.fields.item(0).value
> >
> >

> I told you in my previous message: You need to use a Command object to
> retrieve output and return values. Your stored procedure is not returning

a

> resultset, so there will not be a recordset.
>
> Just to clarify:
> SELECT @var = somevalue
> does not return a resultset: it assigns the value to the variable. Now you
> could create a SELECT statement that returns that value, like this:
> SELECT somevalue as value_returned_in_resultset
>
> but, again, this is wasteful. It is better IMO to use the output

parameter.

>
> The basics:
> There are three ways to return values from a procedure:
> 1. Use a SELECT statement to return a resultset, a set of data in tabular
> format
> 2. Use the Return statement:
> create procedure ReturnSomething AS
> Return 25
> go
> execute ReturnSomething
> or
> declare @retval
> exec @retval = ReturnSomething
> Select @retval as value_returned_by_Return_statement
>
> 3. Use an output parameter
> described in my previous reply
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>