When you think ASP, think...
logo
spgif
search
spgif
site-index
spgif
Recent Articles xml
All Articles
ASP.NET Articles
ASPFAQs.com
Related Web Technologies
User Tips!
Coding Tips
spgif
spgif spgif
spgif

leftnav-resources
spgif
Sections:
Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
spgif
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right
right-bg
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
cmd.parameters(1).value="hello"

'And so on...
cmd.parameters(50).value="gfg"
%>

and your result from store procedure is in

<%=cmd.parameters(0).value%>

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:
https://www.4guysfromrolla.com/webtech/webdaily
*****************************************************************
*****************************************************************

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