Stored Procedure and ASP.. How do I get the values and execute correctly. I need help (easy question, but urgent)

I am trying to write a stored procedure that executes several select statements and returns the values (all integers).  I am starting with only 3 selects, but after I get it working correctly it will contain more queries.  The SP runs fine in the analyzer and I think I have it set up correctly. (I usually do all of my queries and selects in the ASP code.).

I dont know how I need to execute the procedure from ASP as well as how I get the values it returns.  I have read several articles and questions posted here and I have tried several different ways, but I can not seem to get the values.

Also, I want to make sure I am going down the correct path.  This Procedure will end up being incorporated into a loop in my ASP code which cycles through the database selecting various groups and returning the totals which I will output to the screen.  Wouldnt it be the best to have this in a SP?  I dont want to waste all of this time because I could have built the thing in ASP twice already.  This application will have several users at a time hitting it and I want to make it the most efficient.

My SP is as follows:

CREATE PROCEDURE Get_Totals_Branch
(@branch int,
@Total_Records int = Null OUTPUT,
@Total_Units int =  Null OUTPUT,
@Risk_Contracts int = NULL OUTPUT
)
 
AS
DECLARE
SELECT @Total_Records = COUNT(*)FROM fisTkextend where branch = @branch
SELECT @Total_Units = SUM(NumUnits)FROM fisTkextend where branch = @branch
SELECT @Risk_Contracts = count(*) from fisTKExtend where Expire_Date <= getdate() + 451  and branch = @branch
GO







Like I said.  I dont know the easiest or most efficient way to get the data from the SP in ASP.  I have tried several different ways, but I am sure you guys can tell me how to do it.  Thank you in advance.  My deadline is quickly approaching and the frying pan is getting hot.
Jbryant22Asked:
Who is Participating?
 
HilaireConnect With a Mentor Commented:
Oops, more typos in the ASP

' create an ADO Connection
dim dbconn, rs, sql
set dbconn = server.createobject("ADODB.Connection")
' you might need a different connection string if you use integrated security
dbconn.Open( "Provider=sqloledb;Data Source=YourServer;Initial Catalog=pubs;User Id=asplogin;Password=asppassword;")
sql = "exec Get_Totals_Branch " & strBranchID
' exec SP
set rs = dbconn.execute(sql)
'loop through results
while not rs.EOF
   response.write rs("TotalRecords").Value & "<BR>
   rs.MoveNext
wend
rs.close
set rs = nothing

0
 
HilaireCommented:
Returning values in a recordset will be easier than getting the values of the output parameters
Also you could do the same thing with a single query

CREATE PROCEDURE Get_Totals_Branch
(@branch int,
@Total_Records int = Null OUTPUT,
@Total_Units int =  Null OUTPUT,
@Risk_Contracts int = NULL OUTPUT
)
AS
SET NOCOUNT ON
SELECT COUNT(*) as TotalRecords,
   SUM(NumUnits) as TotalUnits,
   SUM(CASE WHEN Expire_Date <= getdate() + 451  then 1 else 0 end) as Risk_Contracts
   FROM fisTKExtend
   WHERE branch = @branch
SET NOCOUNT OFF
GO

Hilaire
0
 
Jbryant22Author Commented:
Cool, I will try exactly that in the SP.  How do I execute and get the info in a RS in ASP?  

I dont know how to get the returned values.  The only time I have used Stored Procedures is for CR and it didnt require me to do much with them.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
HilaireCommented:
Oops, should be

CREATE PROCEDURE Get_Totals_Branch (@branch int)
AS
SET NOCOUNT ON
SELECT COUNT(*) as TotalRecords,
   SUM(NumUnits) as TotalUnits,
   SUM(CASE WHEN Expire_Date <= getdate() + 451  then 1 else 0 end) as Risk_Contracts
   FROM fisTKExtend
   WHERE branch = @branch
SET NOCOUNT OFF
GO

Basically to call it in ASP you just need to
' create an ADO Connection
dim dbconn, rs, sql
set dbconn = server.createobject("ADODB.Connection")
'
dbconn.Open( "Provider=sqloledb;Data Source=YourServer;Initial Catalog=pubs;User Id=asplogin;Password=asppassword;")
sql = "exec Get_Totals_Branch " & strBranchID
' exec SP
set rs = dbconn.execute(sql)
'loop through results
while not trs.EOF
   response.write rs("TotalRecords").Value & "<BR>
   rs.MoveNext
wend
rs.close
set rs = nothing


HTH

Hilaire
0
 
Jbryant22Author Commented:
Thank you very much...
0
 
Jbryant22Author Commented:
Yep, I got that end covered.  So, is it a bad idea to use the Stored procedure approach vs just putting it in asp code?


Thanks again for all of your help!
0
 
HilaireCommented:
>> So, is it a bad idea to use the Stored procedure approach vs just putting it in asp code?<<
Not at all !
Just it depends on what you need to achieve.

Sometimes output parameters are the way to go,
but to retrieve output parameters values you need the ADODB.Command object,
declaring each and every parameter, the code is verbose and there are some little issues very hard to track when you're new to ADO.



0
 
HilaireCommented:
>> So, is it a bad idea to use the Stored procedure approach vs just putting it in asp code?<<
I misread your comment, sorry.

The SQL Code embedded in a stored procedure is pre-compiled. So stored procedures are generally considered to be faster than inline SQL.

With simple queries the difference is unnoticeable.

Stored procedures are very powerful when you want to make several operations in one shot and handle transactions :
when two database operations are logically bound by your business rules, you can group them inside a stored procedure and handle transactions (begin tran, commit , rollback on error) to make sure operationA and operationB stay in synch

begin tran
  update 1
  update 2
  if update 2 fails then rollback update1
  else commit

Hilaire  



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.