ASP/ADO: Return a value from a Stored Procedure

[CJM]

I generally use the following code to call a stored procedure:

sSQL = "Exec MySP " & param1 & ", " & param2
[Set oRS =] oConn.Execute (sSQL)

.... or something like this.

AFAIK it's quite an efficient way to query a DB via ADO - Less overhead than
a command object.

However, I'm not sure how I should handle where I want the SP to return a
value.

For example, I want to do an Insert. I'd like to be able to return one of
several return values:

Scope_Identity() if OK
'1' if record already exists
'0' if any other error

I can do this currently by returning a derived recordset, ie, 'Select
Scope_Identity() as ReturnVal' or 'Select 1 as ReturnVal'.
I can then query this recordset to find out the result.

I know that SP's can have OUTPUT parameters, and that you these can be used
in conjunction with the Command Object/Parameters.

The question is, can my original technique be modified to handle these
output parameters?

If not, what are the relative merits in using a Command object, vs return a
recordset contain the value?

Thanks in advance

[Aaron [SQL Server MVP]]

[url]http://www.aspfaq.com/params.htm[/url]

(FTI, this is one of the few articles on my site not written by me.)

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)




"CJM" <cjmnews04@newsgroups.nospam> wrote in message
news:#pXpJCcaEHA.2840@TK2MSFTNGP11.phx.gbl...

> I generally use the following code to call a stored procedure:
>
> sSQL = "Exec MySP " & param1 & ", " & param2
> [Set oRS =] oConn.Execute (sSQL)
>
> ... or something like this.
>
> AFAIK it's quite an efficient way to query a DB via ADO - Less overhead

than

> a command object.
>
> However, I'm not sure how I should handle where I want the SP to return a
> value.
>
> For example, I want to do an Insert. I'd like to be able to return one of
> several return values:
>
> Scope_Identity() if OK
> '1' if record already exists
> '0' if any other error
>
> I can do this currently by returning a derived recordset, ie, 'Select
> Scope_Identity() as ReturnVal' or 'Select 1 as ReturnVal'.
> I can then query this recordset to find out the result.
>
> I know that SP's can have OUTPUT parameters, and that you these can be

used

> in conjunction with the Command Object/Parameters.
>
> The question is, can my original technique be modified to handle these
> output parameters?
>
> If not, what are the relative merits in using a Command object, vs return

a

> recordset contain the value?
>
> Thanks in advance
>
>

[CJM]

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:e$V1mDcaEHA.3356@tk2msftngp13.phx.gbl...

> [url]http://www.aspfaq.com/params.htm[/url]
>
> (FTI, this is one of the few articles on my site not written by me.)
>

Thanks Aaron.

I take it that the article was a long way of saying 'use the command object
for returned values'? lol. I suspected as much, and it's not a problem
really.

Thanks

Chris

[[MSFT]]

Hi Chris,

I think you original way cannot get a return value from the store
procedure. You have to use Command object and OutPut parameter, or return a
recordset. Based on my experience, there wouldn't be a big lost on
performance when we use command object.

Luke

[CST]

Hopefully I am not missing anything in your text, but can't you just
do a select at the end of your insert statement. Please see psuedo
Stored Proc SQL:

if not exists (select ... from Table where) begin
Insert into Table () Values
select 0 as RetVal, 'OK' as ErrMsg
end else begin
select 1, 'record already exists' as ErrMsg
end

Just add the not exists to your existing stored procs.

HTH

"CJM" <cjmnews04@newsgroups.nospam> wrote in message news:<#pXpJCcaEHA.2840@TK2MSFTNGP11.phx.gbl>...

> I generally use the following code to call a stored procedure:
>
> sSQL = "Exec MySP " & param1 & ", " & param2
> [Set oRS =] oConn.Execute (sSQL)
>
> ... or something like this.
>
> AFAIK it's quite an efficient way to query a DB via ADO - Less overhead than
> a command object.
>
> However, I'm not sure how I should handle where I want the SP to return a
> value.
>
> For example, I want to do an Insert. I'd like to be able to return one of
> several return values:
>
> Scope_Identity() if OK
> '1' if record already exists
> '0' if any other error
>
> I can do this currently by returning a derived recordset, ie, 'Select
> Scope_Identity() as ReturnVal' or 'Select 1 as ReturnVal'.
> I can then query this recordset to find out the result.
>
> I know that SP's can have OUTPUT parameters, and that you these can be used
> in conjunction with the Command Object/Parameters.
>
> The question is, can my original technique be modified to handle these
> output parameters?
>
> If not, what are the relative merits in using a Command object, vs return a
> recordset contain the value?
>
> Thanks in advance

[CJM]

"CST" <cont@b-50.com> wrote in message
news:bd314cc7.0407200342.17361cd3@posting.google.c om...

> Hopefully I am not missing anything in your text, but can't you just
> do a select at the end of your insert statement. Please see psuedo
> Stored Proc SQL:
>
> if not exists (select ... from Table where) begin
> Insert into Table () Values
> select 0 as RetVal, 'OK' as ErrMsg
> end else begin
> select 1, 'record already exists' as ErrMsg
> end
>
> Just add the not exists to your existing stored procs.
>
> HTH

Yes, you can. This is the way I currently do it. I was just wondering if
there are better ways without using a command object, and it appears there
aren't.

Chris

[C T]

You wouldn't have to use the command object. Say the insert statement
returns 2 fields (errnbr, errdesc)

set rs = adoConn.Execute(Some Stored Proc)

strErr = rs("ErrNbr")
strErrDesc = rs("ErrDesc")

rs.close
set rs = nothing

Much easier and a better way than the cmd object.




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

[CJM]

Blimey! This is getting confusing...

I currently use the method that yourself & CST are using! That much is clear
from my original post.

CJM

[C T]

Sorry for the confusion, I was working off of the example provided by
CST (cool initials BTW). Are you still having problems or do you need
clerification on my post?



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

[Bob Barrows [MVP]]

C T wrote:

> You wouldn't have to use the command object. Say the insert statement
> returns 2 fields (errnbr, errdesc)
>
> set rs = adoConn.Execute(Some Stored Proc)
>
> strErr = rs("ErrNbr")
> strErrDesc = rs("ErrDesc")
>
> rs.close
> set rs = nothing
>
> Much easier and a better way than the cmd object.
>

It may be easier (I disagrree that it is "much" easier), but I disagree that
it is better to retrieve a relatively large resultset from the database and
marshal it across processes into a needlessly large COM object (the
recordset) when you could be retrieving two values.

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.

[Aaron [SQL Server MVP]]

> Yes, you can. This is the way I currently do it. I was just wondering if

> there are better ways without using a command object, and it appears there
> aren't.

What do you mean? If you are returning a resultset, and not using output /
return values, then you can avoid a command object.

CREATE PROCEDURE dbo.foo
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT ... FROM table WHERE ...)
BEGIN
INSERT Table VALUES(...)
SELECT 0
ELSE
SELECT 1
END
GO

Now from ASP:

set rs = conn.execute("EXEC dbo.foo")
response.write rs(0)

If you are using explicit output variables or return values, then yes, you
do need to use a command object, because the connection object does not
provide that functionality on its own.

--
[url]http://www.aspfaq.com/[/url]
(Reverse address to reply.)

[Bob Barrows [MVP]]

C T wrote:

> You wouldn't have to use the command object. Say the insert statement
> returns 2 fields (errnbr, errdesc)
>
> set rs = adoConn.Execute(Some Stored Proc)
>
> strErr = rs("ErrNbr")
> strErrDesc = rs("ErrDesc")
>
> rs.close
> set rs = nothing
>
> Much easier and a better way than the cmd object.
>
>

I forgot to mention that this method leaves you open to SQL Injection. As
seen in these links,

[url]http://www.nextgenss.com/papers/advanced_sql_injection.pdf[/url]
[url]http://www.nextgenss.com/papers/more_advanced_sql_injection[/url].

even the Replace() defensive measure that is so often touted can be
defeated. The best way to prevent SQL Injection is to use a Command object
to pass parameters (or, if no return or output parameters are involved, the
"stored-procedure-as-connection-method")

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.

[C T]

Not to sound like an arse, but I don't think that CJM is returning a
large resultset. I may be wrong, but I think that it is just 1 record
stating if the insert was successful or not. I don't think that this
will have that big of an impact on performance. Then again, I don't
fully understand the differences between overhead for both the exec vs
the cmd object.



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

[Bob Barrows [MVP]]

C T wrote:

> Not to sound like an arse, but I don't think that CJM is returning a
> large resultset.

Any resultset is large compared to the data it contains, especially if it
contains only numeric data (if you're retrieving very large text fields,
then the data may exceed the metadata). It is always expensive to marshal a
resultset across processes into a COM recordset.

Recordsets are designed to be used where scrolling is necessary, i.e., where
multiple records are involved. They are overkill when you are retrieving a
few numeric values in a single record.

It is up to the developer what kinds of tradeoffs re. performance,
resources, and ease of programming he wishes to make. Just remember that on
a web server, an application is not working in isolation. While an app may
be performing adequately when run by itself, it may impact performance of
the web server as a whole.

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.

[CJM]

This thread doesnt seem to be dying a death, but I think my original
question has been answered.

To summarise, there are two suggested methods:

1) Using a Command object/output parameters
2) Using a simple Select statement to return a value, eg. 'Select 1'

From what I understand, there is not a lot in it. A Command object may be
unnecessary for many operations, and there clearly are ways to avoid using
them. However, Bob suggests that using a Recordset to return a single value
is not making efficient use of the [large] Recordset object either.

So I guess you have to look at each situation individually, and choose an
approach based on the contributing factors. In most cases for me, I dont
suppose it makes much of a difference. As such, I intend to use both
approaches, if only to make sure I am up to speed on each technique, should
I need to be more discriminating in the future.

I home this summarises it well.

Thanks to all.

Chris

[Yan-Hong Huang[MSFT]]

Hi Chris,

Thanks very much for sharing it in the community. :)

Best regards,
Yanhong Huang
Microsoft Community Support

Get Secure! ¨C [url]www.microsoft.com/security[/url]
Register to Access MSDN Managed Newsgroups!
-http://support.microsoft.com/default.aspx?scid=/servicedesks/msdn/nospam.as
p&SD=msdn

This posting is provided "AS IS" with no warranties, and confers no rights.