Best Way to Call Stored Procedures from ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best Way to Call Stored Procedures from ASP

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-01 : 08:35:49

Nath writes "Hi Team,

I'm just curious as to the most efficient way to call stored procedures from ASP.

I am used to using this kind of syntax:

strSQL = "dbo.spDeleteArticle 17"
objDBConn.execute(strSQL)

However, I have seen many examples where, instead of generating a string containing the stored procedure name and any parameters required, parameters are appendend to the ADO Parameters collection and then the stored procedure is executed.

Apart from extra lines of ADO code in ASP, what are the differences in execution and performance of using AddParameter instead of a dynamically generated string?

thanks yet again Team!

Nath"

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-01 : 08:51:36

quote:

Nath writes "Hi Team,

I'm just curious as to the most efficient way to call stored procedures from ASP.

I am used to using this kind of syntax:

strSQL = "dbo.spDeleteArticle 17"
objDBConn.execute(strSQL)

However, I have seen many examples where, instead of generating a string containing the stored procedure name and any parameters required, parameters are appendend to the ADO Parameters collection and then the stored procedure is executed.

Apart from extra lines of ADO code in ASP, what are the differences in execution and performance of using AddParameter instead of a dynamically generated string?

thanks yet again Team!

Nath"

Whenever I did ASP --> SQL stuff I used to use the string method, however (I'm not 100% on this but) I think that the .net method uses the parameterised version, and the paramaterized version is less susceptible to hacking, so you may be best off learning that one. I'm not sure if there's any performance benefit to either.

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-01 : 10:35:37

I've used both methods. Each has a benefit.

In ASP

SQL = "EXEC dbo.mysp @parm1=123,@parm2='myid'"
SET rs = dataconn.execute(SQL)

Benefits: Quick coding, and the query can be passed to another routine if needed (like to do a printable report, download CSV, email everyone in query, etc.)

Downside: I've never figured out how to get an output parameter or return value using this method.
--------
In Asp/Ado

on error resume next
DIM cmdAdminInsert
SET cmdAdminInsert = Server.CreateObject("ADODB.Command")
WITH cmdAdminInsert
.ActiveConnection = application("DBaddr")
.CommandText = "dbo.AD_AdminInsert"
.CommandType = adcmdstoredproc
.Parameters.Append .CreateParameter ("RETURN_VALUE", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter ("@CallerAdminID", adInteger, adParamInput, , Session("AdminID"))
.Parameters.Append .CreateParameter ("@Username", adChar, adParamInput, 100, usernameinput)
.Parameters.Append .CreateParameter ("@Password", adChar, adParamInput, 100, passwordinput)
.Execute ,,adExecuteNoRecords
' Get the Output Parameter and return value
SQLResult = .Parameters ("@OutputResult")
ReturnValue = .Parameters("RETURN_VALUE")
If CheckADOErrors(.ActiveConnection) Then
' Nothing to do
End If
End With
SET cmdAdminInsert = Nothing
--------------------
This method is great if an Output parameter or return value is needed, and it also type-checks the parameters. *I think* this is a cleaner way of coding a parameter list than the string method.

Downside: Cannot pass this query to another routine. The order of the Parameters are not always independent as they should be. I have had to code EVERY parameter, and in the same order as the parameters are declared in the SP.

This is a good link to a page out of a Wrox book on ADO at

[url]http://www.asp101.com/articles/wrox/asp30/26100903.asp[/url]

Sam

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-11-01 : 11:21:18

In general, when trying to return values from your SQL code, connection.execute is not a good choice. However, if you have, lets say, a stored procedure that performs a task and returns nothing, I always use connection.execute for this, as it makes things more simple.

***************************************
Death must absolutely come to enemies of the code!

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-11-01 : 13:34:58

The ADODB.Command method (with fully supplied parameters; don't use "refresh") is far and away superior for everything except coding ease. It type checks parameters, it allows to to specify cursor and lock types, it puts the datatypes right into your code so you can eyeball an error if you're assigning "textvalue" to an adInteger field.

Plus, the command object doen't cause a t-sql compile on the server, while the Connection.Execute batch is actually just a t-sql command ("exec dbo.spDeleteArticle 17"), which causes a compile.

Also, if you use the ADODB.Command approach and your stored procedure only returns output values (or nothing), you gain additional performance by using the adExecuteNoRecords option -- which means ADO doesn't have to create a recordset on the web server.

I admit that I have a few connection.executes lurking in my applications, but never ever in anyplace where performance matters, and not on high volume pages.

Cheers
-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources