Stored Procedure Output using ASP

[m3ckon]

Hi,

I've written an sproc which inserts customer data into a SQL table, then
stores a variable as the unique id of the row (@orderid) ... then
inserts data into abother table using this parameter @orderid

The problem I have is although I can execute this sproc from my asp code
... I need to output the value of @orderid to my asp page and I don't
know how to do this.

I run the sproc by using this code:

set dborders = server.createobject("ADODB.Connection")
dborders.open "Driver={SQL
Server};Server=orac;Database=database;Uid=user;Pwd =password;"

strsql = "exec transfersproc" & chr(39)& session.sessionid & chr(39)
& chr(44) & _
chr(39) & strcustomername & chr(39)& chr(44) & _
chr(39) & strcompanyname & chr(39)& chr(44) & _
chr(39) & strcustomerEmail & chr(39)& chr(44) & _
chr(39) & strcustomeraddress & chr(39)& chr(44) & _
chr(39) & strcustomeraddress2 & chr(39)& chr(44) & _
chr(39) & strcustomeraddress3 & chr(39)& chr(44) & _
chr(39) & strcustomercity & chr(39)& chr(44) & _
chr(39) & strcustomerPostCode & chr(39)& chr(44) & _
chr(39) & strcustomerphone & chr(39)& chr(44) & _
chr(39) & strcustomermobile & chr(39)& chr(44) & _
chr(39) & strordertotal & chr(39)& chr(44) & _
chr(39) & strbusinessuse & chr(39)& chr(44) & _
chr(39) & stremployees & chr(39)& chr(44) & _
chr(39) & straboutus & chr(39)& chr(44) & _
chr(39) & strtextdispatch & chr(39)& chr(44) & _
chr(39) & strtextoffers & chr(39)& chr(44)


can someone please tell me what I need to add to my sproc to get an
output parameter and also how I access it in my code ... please??

M3ckon

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Bob Barrows [MVP]]

m3ckon wrote:

> Hi,
>

> can someone please tell me what I need to add to my sproc to get an
> output parameter and also how I access it in my code ... please??
>

Read the following text from my FAQ folder and get back to us to tell us the
method you wish to use to return the value.

To help your decision, methods 2 and 3 require the use of a Command object,
which can be troublesome to write code for. Fortunately, free code
generators have been written, including mine which is available at

[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

************************************************** **
There are 3 ways to return values from a SQL Server stored procedure:
1. a Select statement that returns a resultset
--run this script in Query Analyzer (QA):
Create Procedure SelectValue
(@input int)
AS
Select @input + 5
go
exec SelectValue 10
go
drop procedure SelectValue


2. a Return parameter:
--run this script in QA:
create procedure ReturnValue
(@input int)
AS
Return @input + 5
go
declare @returnvalue int
exec @returnvalue = ReturnValue 10
select @returnvalue
go
drop procedure ReturnValue

3. an Output Parameter:
--run this script in QA:
create procedure OutputValue
(@input int output)
AS
SET @input = @input + 5
go
declare @outputvalue int
SET @outputvalue = 10
exec OutputValue @outputvalue output
select @outputvalue
go
drop procedure OutputValue


I do not recommend method 1 for returning a single value. A resultset is
expensive to build, in that it must contain metadata in addition to data. So
more network traffic is created, and the client app needs to expend more
resources in order to retrieve and expose the resultset to the calling
procedure.

Most developers use the Return parameter to return status codes instead of
data. This is for the sake of consistency: there is no technical reason not
to use RETURN to return data, except that RETURN can only be used to return
integers. If you need to return other datatypes, you need to use an output
parameter.
************************************************** *****



--
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"