Stored Procedures

[Lana Mosby]

Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.

[Mark]

Hi Lana,

Yes, See a SQL Server Stored Procedure example at APP-10 at:
http://www.aspdb.com/V2

Mark.


------------
Lana Mosby at 11/15/00 12:10:49 PM


Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.

[Lana Mosby]

Which example illustrates calling a stored procedure?

------------
Mark at 11/15/00 12:21:34 PM

Hi Lana,

Yes, See a SQL Server Stored Procedure example at APP-10 at:
http://www.aspdb.com/V2

Mark.


------------
Lana Mosby at 11/15/00 12:10:49 PM


Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.

[Mark]

APP-10
Mark.


------------
Lana Mosby at 11/15/00 12:28:41 PM


Which example illustrates calling a stored procedure?

------------
Mark at 11/15/00 12:21:34 PM

Hi Lana,

Yes, See a SQL Server Stored Procedure example at APP-10 at:
http://www.aspdb.com/V2

Mark.


------------
Lana Mosby at 11/15/00 12:10:49 PM


Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.

[Lana Mosby]

I tried executing the stored procedure below, but nothing happens. My browser just hangs up.
<%
dim varapproved
varapproved = &#34;Disapproved&#34;
thisname = &#34;Lana Test&#34;
if varapproved = &#34;Disapproved&#34; then
Set Mydb = Server.CreateObject(&#34;Asp.db&#34
Mydb.dbDSN = &#34;Provider=SQLOLEDB; Data Source=SQL1; Initial Catalog=CATES; User ID=&#34; & session(&#34;username&#34 & &#34;; password=&#34; & session(&#34;userpassword&#34& &#34;;&#34;
Mydb.dbDBType = &#34;SQL&#34;
Mydb.dbDisplay = False
mydb.dbStoredProcCmdParams = &#34;[usp_insertrec_disapproved];@varchemical,200,1,50,&#39;&thisname&&#39;&#34;
Mydb.aspdb
End If
response.write &#34;Completed&#34;




------------
Mark at 11/15/00 12:39:57 PM

APP-10
Mark.


------------
Lana Mosby at 11/15/00 12:28:41 PM


Which example illustrates calling a stored procedure?

------------
Mark at 11/15/00 12:21:34 PM

Hi Lana,

Yes, See a SQL Server Stored Procedure example at APP-10 at:
http://www.aspdb.com/V2

Mark.


------------
Lana Mosby at 11/15/00 12:10:49 PM


Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.

[Lana Mosby]

I think the problem has to do with me inserting a record as oppose to just querying. Have you tried executing a stored procedure with an insert, update, or delete, passing in parameters?

------------
John at 11/16/00 1:58:53 AM

Hi Lana,

Here&#39;s a stored procedure that has worked in the past that uses the PUBS database that expects a text (varchar) parameter. It uses that parameter to select a Last Name from the Authors Table. We used &#34;G%&#34; for ours (all names starting with G). Try getting it to work on your system, then modify your SP to work the same way.


Here&#39;s the Stored Procedure:

CREATE PROCEDURE sp_text @nam varchar(99)
AS
select * from authors
where authors.au_lname LIKE @nam


Here&#39;s a sample SQL Server Analyzer query that calls it to verify that it works (should return 3 records)

execute sp_text &#39;G%&#39;


And finally, here&#39;s an ASP-db program that uses dbStoredProcCmdParams to call it and pass it &#34;G%&#34;:

<%
Set X= Server.CreateObject(&#34;ASP.DB&#34
X.dbUnit = &#34;999&#34;
X.dbMode = &#34;Grid&#34;
X.dbDSN = &#34;DSN=PUBS&#34;
X.dbStoredProcCmdParams = &#34;(;,)sp_text,4;@nam,200,1,99,&#39;G%&#39;&# 34;
&#39;X.dbStoredProc= &#34;sp_text,,&#39;G%&#39;&#34; &#39; This also WORKS !
X.ASPdb
%>

A couple of notes:

1) As the docs say, you MUST wrap any text parameters in single quotes
2) If you call ASP-db with illegal parameters, it will take a LONG time to &#34;timeout&#34;. The quick way to get your system back (get rid of the hourglass), is to bring up the Task Manager (CTRL-ALT-DEL) and get rid of the MTX (in NT) or DLLHOST (in Windows 2000) task that&#39;s taking up all your CPU. Just click on the line item and choose End Process. Then, your next &#34;correct&#34; ASP-db query will execute at full speed.
3) The first ASP-db Stored Proc call might take up to 15 seconds, and then subsequent ones only take 1 second or so.
4) The example used 99 for the width. Just be sure it is at least as big as the field width.

Hope this helps point you in the right direction!

Thanks,
John



------------
Lana Mosby at 11/15/00 2:50:19 PM


I tried executing the stored procedure below, but nothing happens. My browser just hangs up.
<%
dim varapproved
varapproved = &#34;Disapproved&#34;
thisname = &#34;Lana Test&#34;
if varapproved = &#34;Disapproved&#34; then
Set Mydb = Server.CreateObject(&#34;Asp.db&#34
Mydb.dbDSN = &#34;Provider=SQLOLEDB; Data Source=SQL1; Initial Catalog=CATES; User ID=&#34; & session(&#34;username&#34 & &#34;; password=&#34; & session(&#34;userpassword&#34& &#34;;&#34;
Mydb.dbDBType = &#34;SQL&#34;
Mydb.dbDisplay = False
mydb.dbStoredProcCmdParams = &#34;[usp_insertrec_disapproved];@varchemical,200,1,50,&#39;&thisname&&#39;&#34;
Mydb.aspdb
End If
response.write &#34;Completed&#34;




------------
Mark at 11/15/00 12:39:57 PM

APP-10
Mark.


------------
Lana Mosby at 11/15/00 12:28:41 PM


Which example illustrates calling a stored procedure?

------------
Mark at 11/15/00 12:21:34 PM

Hi Lana,

Yes, See a SQL Server Stored Procedure example at APP-10 at:
http://www.aspdb.com/V2

Mark.


------------
Lana Mosby at 11/15/00 12:10:49 PM


Are there any examples on calling stored procedures from within aspdb. If so where are they.

I am using SQL Server to create my stored procedures.