Solved

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

Posted on 2004-09-17
8
1,106 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Jbryant22
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12085552
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
 

Author Comment

by:Jbryant22
ID: 12085611
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12085652
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
 

Author Comment

by:Jbryant22
ID: 12085677
Thank you very much...
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12085709
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
 

Author Comment

by:Jbryant22
ID: 12085870
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12085972
>> 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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12086067
>> 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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now