KnightWare wroteCan someone describe the steps to call an external (MS SQL) stored procedure that returns a results set.
A nice step by step would be nice as I'm not wrapping my head around this one.
This is # 1 on Bruce's 2 methods.
In your reply, you were doing the simpler Bruce method # 2. Problem is, its limiting. you can filter or sort. You don't really have a good result set you can manipulate.
Examine this. For now, as you can see, there are no parms. Simple. The menu runs this process:
The SP:
ALTER PROCEDURE [dbo].[SP_SystemINOUT]
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM dbo.SYSTEMINOUT
INSERT INTO SYSTEMINOUT
([ID]
,[BASVERSION]
,[BASTIMESTAMP]
,[theID]
,[TransDate]
,[InOut2]
,[LicID]
,[Name]
,[Type]
,[OffID]
,[Offname]
)
select T.ID, 1, getdate(), T.ID, T.TransDate, InOut2, T.ps_Licensee_RID ,
L.Name, L.Type, T.ps_Office_RID, O.OfficeName
from TRANS T inner join OFFICE O on T.ps_Office_RID = O.ID
inner join LICENSEE L on L.ID = T.ps_Licensee_RID
where InOut2 = 'OUT'
ORDER BY T.ID DESC
END
My Query for this just shows all recs from this REAL MS SQL table "SystemINOUT".
You'd modify this to accept your search parms and @UserID.
(Make sure you EXEC_SP LoggedInRegularUser.ID, NOT LoggedInRegularUser )
Here's an example from another of my SP calls:
EXEC_SP 'SP_DelDupLeadsFromUserList' WITH
'@ListID' = LoggedInRegularUser.ps_DefaultList.ID,
'@UserID' = LoggedInRegularUser.ID,
'@NumRecs' = UserListMaster.theQty OUT
Delete any prior values for the calling user - cause 2+ users could be using this at the same time.
Insert recs into your PPED table, plus the @UserID.
The Query is FIND PPED WHERE PPED.userid = LoggedInRegularUser.ID
Then you can sort/filter/mark/export/etc. these recs and do whatever you want with them.
They get rebuilt when you re-run your Process.
jaymer...
