Value from store procedure

In my store procedure i have variable and i assing to him diferent values.
how can i pass this value to variable in my asp code.
thanks

Re: Value from store procedure

viktor wrote:
> In my store procedure i have variable and i assing to him diferent
> values. how can i pass this value to variable in my asp code.
> thanks

http://tinyurl.com/jyy0

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"

Re: Value from store procedure

viktor wrote:
> In my store procedure i have variable and i assing to him diferent
> values. how can i pass this value to variable in my asp code.
> thanks
Oh wait. You're asking how to return a value from a stored procedure to
ASP? If so:

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.

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"

Re: Value from store procedure

thanks
"Bob Barrows [MVP]" wrote in message
news:%23tN2X%23x2EHA.3392@TK2MSFTNGP10.phx.gbl...
> viktor wrote:
>> In my store procedure i have variable and i assing to him diferent
>> values. how can i pass this value to variable in my asp code.
>> thanks
> Oh wait. You're asking how to return a value from a stored procedure to
> ASP? If so:
>
> 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.
>
> 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"
>

Re: Value from store procedure

"Bob Barrows [MVP]" wrote in message
news:%23tN2X%23x2EHA.3392@TK2MSFTNGP10.phx.gbl...
: viktor wrote:
: > In my store procedure i have variable and i assing to him diferent
: > values. how can i pass this value to variable in my asp code.
: > thanks
: Oh wait. You're asking how to return a value from a stored procedure to
: ASP? If so:
:
: 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.

That's odd. I have a simple stored procedure that takes a lookup value and
return a row with two columns. I haven't written the ASP code yet. I'm
running this in VS.NET 2K3. Would that make a difference?

CREATE PROCEDURE dbo.SelectUSDomain
(
@usdomain nvarchar(50)
)
AS
SET NOCOUNT ON
SELECT
usdomain,
usdata
FROM ustable NOLOCK
WHERE usdomain = @usdomain
DROP PROCEDURE dbo.SelectUSDomain

This is my output:

Running dbo."SelectUSDomain" ( @usdomain = localhost.us ).usdomain usdata
------------ --------------------------------------
localhost.us illegal domain
No more results.
(1 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."SelectUSDomain".

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Value from store procedure

Roland Hall wrote:
>>
>> 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.
>
> That's odd.

What's odd?

> I have a simple stored procedure that takes a lookup
> value and return a row with two columns. I haven't written the ASP
> code yet. I'm running this in VS.NET 2K3. Would that make a
> difference?

To what? I'm not sure what you are questioning.

>
> CREATE PROCEDURE dbo.SelectUSDomain
> (
> @usdomain nvarchar(50)
> )
> AS
> SET NOCOUNT ON
> SELECT
> usdomain,
> usdata
> FROM ustable NOLOCK
> WHERE usdomain = @usdomain
> DROP PROCEDURE dbo.SelectUSDomain
>

OK, you are using method 1 (a resultset) to return your two values instead
of using output parameters.

> This is my output:
>
> Running dbo."SelectUSDomain" ( @usdomain = localhost.us ).usdomain
> usdata ------------ --------------------------------------
> localhost.us illegal domain
> No more results.
> (1 row(s) returned)
> @RETURN_VALUE = 0
> Finished running dbo."SelectUSDomain".

What's the problem?

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"

Re: Value from store procedure

"Bob Barrows [MVP]" wrote in message
news:ed8P%23uQ4EHA.3380@TK2MSFTNGP09.phx.gbl...
: Roland Hall wrote:
: >>
: >> 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.
: >
: > That's odd.
:
: What's odd?

Odd that I'm not using a return.

: > I have a simple stored procedure that takes a lookup
: > value and return a row with two columns. I haven't written the ASP
: > code yet. I'm running this in VS.NET 2K3. Would that make a
: > difference?
:
: To what? I'm not sure what you are questioning.

Will I need to use the output method to get a return of my data?

: > CREATE PROCEDURE dbo.SelectUSDomain
: > (
: > @usdomain nvarchar(50)
: > )
: > AS
: > SET NOCOUNT ON
: > SELECT
: > usdomain,
: > usdata
: > FROM ustable NOLOCK
: > WHERE usdomain = @usdomain
: > DROP PROCEDURE dbo.SelectUSDomain
: >
:
: OK, you are using method 1 (a resultset) to return your two values instead
: of using output parameters.
:
: > This is my output:
: >
: > Running dbo."SelectUSDomain" ( @usdomain = localhost.us ).usdomain
: > usdata ------------ --------------------------------------
: > localhost.us illegal domain
: > No more results.
: > (1 row(s) returned)
: > @RETURN_VALUE = 0
: > Finished running dbo."SelectUSDomain".
:
: What's the problem?

Nothing inside of VS.NET using Server Explorer, just didn't know if I needed
to make a change once I call it from an .aspx page. I know ADO pretty well
but ADO.NET and code behind is foreign to me. I'm in shark infested waters
and my boat has a leak.

Can you recommend a good reference on calling stored procedures from
ado.net? I'm searching but most of what I find assumes I know what I'm
doing. The transition from ASP -> ASP.NET bring to mind a Marisa Tomei line
in My Cousin Vinny "Oh my God, what a fricken nightmare."

TIA

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Value from store procedure

Roland Hall wrote:
> "Bob Barrows [MVP]" wrote in message
> news:ed8P%23uQ4EHA.3380@TK2MSFTNGP09.phx.gbl...
>> Roland Hall wrote:
>>>>
>>>> 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.
>>>
>>> That's odd.
>>
>> What's odd?
>
> Odd that I'm not using a return.

That was your choice. I outlined 3 methods for returning values from a
stored procedure: resultset, return parameter, and output parameter. It's
your choice as to which method to use.

>
>>> I have a simple stored procedure that takes a lookup
>>> value and return a row with two columns. I haven't written the ASP
>>> code yet. I'm running this in VS.NET 2K3. Would that make a
>>> difference?
>>
>> To what? I'm not sure what you are questioning.
>
> Will I need to use the output method to get a return of my data?

No, it's just a resultset. You would retrieve these results into a
recordset. If you use output parameters (method 3 in my OP), then you need
to use a Command to get the results. I'm not quite up-to-speed on dotnet as
yet, so I can't get into any details as far as the terminology needed in
..Net. In asp, of course, you could run this procedure like this:

set rs=createobject("adodb.recordset")
conn.SelectUSDomain localhost.us, rs

This will not work in .Net.

Alternatively, you can use the technique described here:
http://www.aspfaq.com/show.asp?id=2201
My comments on this are here:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/563f6ac9b41090f9

> Nothing inside of VS.NET using Server Explorer, just didn't know if I
> needed to make a change once I call it from an .aspx page. I know
> ADO pretty well but ADO.NET and code behind is foreign to me. I'm in
> shark infested waters and my boat has a leak.
>
> Can you recommend a good reference on calling stored procedures from
> ado.net? I'm searching but most of what I find assumes I know what
> I'm doing. The transition from ASP -> ASP.NET bring to mind a Marisa
> Tomei line in My Cousin Vinny "Oh my God, what a fricken nightmare."
>

I haven't started reading up on ado.net yet, but have you looked at
msdn.microsoft.com/library? All the documentation for ado.net can be found
there. Here's something:
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/cpguide/html/cpconretrievingidentityorautonumbervalues. asp

There are more to be found there.

You may want to direct future dotnet questions to the appropriate "dotnet"
group. They all have the word "dotnet" in their names.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Value from store procedure

"Bob Barrows [MVP]" wrote in message
news:%23eUKkbR4EHA.1452@TK2MSFTNGP11.phx.gbl...
: Roland Hall wrote:
: > "Bob Barrows [MVP]" wrote in message
: > news:ed8P%23uQ4EHA.3380@TK2MSFTNGP09.phx.gbl...
: >> Roland Hall wrote:
: >>>>
: >>>> 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.
: >>>
: >>> That's odd.
: >>
: >> What's odd?
: >
: > Odd that I'm not using a return.
:
: That was your choice. I outlined 3 methods for returning values from a
: stored procedure: resultset, return parameter, and output parameter. It's
: your choice as to which method to use.
:
: >
: >>> I have a simple stored procedure that takes a lookup
: >>> value and return a row with two columns. I haven't written the ASP
: >>> code yet. I'm running this in VS.NET 2K3. Would that make a
: >>> difference?
: >>
: >> To what? I'm not sure what you are questioning.
: >
: > Will I need to use the output method to get a return of my data?
:
: No, it's just a resultset. You would retrieve these results into a
: recordset. If you use output parameters (method 3 in my OP), then you need
: to use a Command to get the results. I'm not quite up-to-speed on dotnet
as
: yet, so I can't get into any details as far as the terminology needed in
: .Net. In asp, of course, you could run this procedure like this:
:
: set rs=createobject("adodb.recordset")
: conn.SelectUSDomain localhost.us, rs
:
: This will not work in .Net.
:
: Alternatively, you can use the technique described here:
: http://www.aspfaq.com/show.asp?id=2201
: My comments on this are here:
:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/563f6ac9b41090f9
:
: > Nothing inside of VS.NET using Server Explorer, just didn't know if I
: > needed to make a change once I call it from an .aspx page. I know
: > ADO pretty well but ADO.NET and code behind is foreign to me. I'm in
: > shark infested waters and my boat has a leak.
: >
: > Can you recommend a good reference on calling stored procedures from
: > ado.net? I'm searching but most of what I find assumes I know what
: > I'm doing. The transition from ASP -> ASP.NET bring to mind a Marisa
: > Tomei line in My Cousin Vinny "Oh my God, what a fricken nightmare."
: >
:
: I haven't started reading up on ado.net yet, but have you looked at
: msdn.microsoft.com/library? All the documentation for ado.net can be found
: there. Here's something:
:
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/cpguide/html/cpconretrievingidentityorautonumbervalues. asp
:
: There are more to be found there.
:
: You may want to direct future dotnet questions to the appropriate "dotnet"
: group. They all have the word "dotnet" in their names.

Thanks Bob

Re: Value from store procedure

Roland Hall wrote:
>> I haven't started reading up on ado.net yet, but have you looked at
>> msdn.microsoft.com/library? All the documentation for ado.net can be
>> found there. Here's something:
>>
>
http://msdn.microsoft.com/library/default.asp?url=/library/e n-us/cpguide/html/cpconretrievingidentityorautonumbervalues. asp
>>
>> There are more to be found there.
>>

I just spotted this. It seems to be more relevant:
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconus ingstoredprocedureswithcommand.asp

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.