November 1, 2005 at 6:34 am
I am pretty new to using Access as a front end to SQL, so appologies if this is a dumb question.
I have an Access adp file and a load of Stored procedures, all fine up to this point. I then need to write another SP that in turn calls aon of the existing SP's as it needs the results to work.
How do I do this? am i approaching this in the wrong way?
Any help most greatfully received.
November 1, 2005 at 7:23 am
in a stored procedure
CREATE PROCEDURE USP_DOSOMEWORK
AS
EXECUTE USP_STEP1
EXECUTE USP_STEP2
...
November 1, 2005 at 8:21 am
Hi thanks for the quick reply
I have tried the above, but when I run USP_DOSOMEWORK it only runs the first step any ideas?
November 1, 2005 at 8:31 am
How do you verify it only runs the first step?
I have no background of access newer than 2000.
Probably it is returning multiple recordsets and your procedure only browses the first. I assume there is a command like getnextresultset
November 1, 2005 at 9:44 am
Basically it returns the same data as if you run the first SP on its own, this is what each of the SP's consists of:
The main SP called sp_DoSomeWork
ALTER PROCEDURE SP_DoSomeWork
AS
EXECUTE SP_DuplicateCheck
EXECUTE SP_names
SP_DuplicateCheck (its this one that it returns the values of)
SELECT TOP 100 PERCENT Surname, PCode, GuestID, OPCID, Title, FirstName, Company, Address1, TelSupplied, Email
FROM dbo.Tbl_Guests
WHERE (Surname IN
(SELECT [Surname]
FROM [Tbl_Guests] AS Tmp
GROUP BY [Surname], [PCode], [OPCID]
HAVING COUNT(*) > 1 AND [PCode] = [Tbl_Guests].[PCode] AND [OPCID] = [Tbl_Guests].[OPCID]))
ORDER BY Surname, PCode
SP_names
ALTER PROCEDURE porsche_launch.SP_names
AS SELECT dbo.Tbl_OPCDetails.DropDownName, dbo.Tbl_Guests.GuestID, dbo.Tbl_Guests.Title, dbo.Tbl_Guests.FirstName, dbo.Tbl_Guests.Surname,
dbo.Tbl_Guests.EventInvited, dbo.Tbl_Guests.InviteType, dbo.Tbl_Guests.MailingStatus,
(CASE WHEN (Tbl_Guests.GuestID = SP_DuplicateCheck.GuestID) THEN N'Yes' ELSE N'No' END) AS Duplicate
FROM dbo.Tbl_Guests LEFT OUTER JOIN
SP_DuplicateCheck ON dbo.Tbl_Guests.GuestID = SP_DuplicateCheck.GuestID LEFT OUTER JOIN
dbo.Tbl_OPCDetails ON dbo.Tbl_Guests.OPCID = dbo.Tbl_OPCDetails.OPCID
WHERE (dbo.Tbl_Guests.InviteType = 1) AND (dbo.Tbl_Guests.MailingStatus = 5)
Thanks in advance for any help offered
November 1, 2005 at 11:42 am
are you using ADO's NextResultSet ?
from http://www.windowsitpro.com/SQLServer/Article/ArticleID/27576/27576.html
When I have to extract data from multiple tables, which approach is more efficient: creating one stored procedure that uses ADO's NextResultSet method to return all the result sets or creating a stored procedure for each result set?
A. The efficiency of these solutions depends on several factors. Creating one stored procedure that returns all the result sets is effective as long as you use NextRecordSet (the ADO Recordset method call to fetch the next set of results) to process the result sets quickly. Failure to process the results quickly can lead to extended locking on the server.
Creating a stored procedure for each result set works well because it permits code reuse. You can let other programs access the data by using each stored procedure as a standalone API without having to worry about coding the other programs to process result sets they might not want. You can still obtain multiple recordsets by combining the calls to each smaller stored procedure in a wrapper stored procedure that contains no logic other than passing parameters to each procedure and error handling. This solution also lets you use smaller stored procedures that compile faster if the need for recompilation arises. Because of the high cost of compilation, we recommend this modular approach.
Say you're building an application that's going to be used on a network with high network latency between the client and the server—for example, on a network with user sites connected over low bandwidth or multiple router hops. In that case, using the ADO Command object with bound parameters to call each stored procedure individually can be more efficient because binding parameters avoids the metadata chitchat that ADO engages in while it determines which data types are being returned from the server. We tend to use this technique when we're conducting performance benchmarks.
just a small remark
why you used TOP 100 PERCENT when it is not a view?
November 2, 2005 at 2:35 am
sounds to me like your access is independant of the problem, the calling a SP from inside another SP is purly sql server, so don't worry about the access bit.
If things arent working, then I would simplify - e.g. comment out functionality and see if things are executing ok, thens tart commenting back in till it `breaks`
martin
p.s.
if i remember rightly, you shouldnt call your stored procedures sp_<name> - its slower accessing them than if there called something else!
November 2, 2005 at 8:49 am
Just a thought, can you get it to run correctly in Query Analyzer?
November 2, 2005 at 10:42 am
Cheers Martin works a treat now
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply