Finance Developer (IL) Next Step Systems US-IL-Chicago Post A Job | Post A Resume

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

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

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