When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Tuesday, November 17, 1998

WebDaily: Your daily source for Web Technology Tips and Tricks!

Using Parameterized Queries

* This article discusses how to use parameterized stored procedures using Acive Server Pages.

In the last WebDaily emailing, I discussed several tips on how to optimize your ASP code for performance. One technique I mentioned was to use SQL stored procedures. Using stored procedures will not only make your code more maintainable (through yet another layer of abstraction between your data model and code), but will also increase performance, since SQL caches your stored procedure's execution plans when they are executed for the first time.

In the last WebDaily emailing I also discussed how to explicitly call a stored procedure in ASP. The example I gave looked like this:


'Create connection
Set rs = Conn.Execute("sp_MyFirstSP " & Param1 & "," & _
     Param2 & "," & ParamN)

I mentioned that there was a Parameters collection in Active Server Pages to streamline this process. Over the weekend Muhammad Atif submitted the following tutorial on how to call stored procedures through ASP: (If you'd like to submit a tip (as it is greatly, GREATLY appreciated), please visit the Add a Tip page.)

You can call store procedures in ASP like this


set conn=server.createObject(ADODB.Connection)
conn.open "dsn=local uid= pwd="

set cmd=server.createobject("ADODB.Command")
set cmd.activeconnection=conn
set cmd.commandtext="StoreProcedure name"
set cmd.commandtype=adcmdStoredProc

'Now you can pass the parameters as of your like

'And so on...

and your result from store procedure is in


if you want it in resultset than

<% set q=cmd.execute %>
<% =q(0)%>
<% =q(1)%>

where 0 and 1 are fields of table

(You can also use the associative names (the string representations for your recordset columns) that you're use to using.)

Thanks for the tip Mr. Atif!! If you have any questions or comments, or ideas about future WebDaily articles, please email me at mitchell@4guysfromrolla.com. Have a great day!

Happy Programming!

To subscribe to WebDaily, point your browser to:

Please forward this information to a friend, including the subscription URL! Happy Programming!

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article