Jbryant22
asked on
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.
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.
ASKER
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.
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.
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
'
dbconn.Open( "Provider=sqloledb;Data Source=YourServer;Initial Catalog=pubs;User Id=asplogin;Password=asppa
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
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
'
dbconn.Open( "Provider=sqloledb;Data Source=YourServer;Initial Catalog=pubs;User Id=asplogin;Password=asppa
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
ASKER
Thank you very much...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks again for all of your help!
>> 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.
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.
>> 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
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
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