I dont know if this is possible or not so I thought I would post it here and see what information can be received.
I have built a Stored Proceedure in MS SQL, this proceedure will get its data from a massive table (currently the table is 2108905 records long), it will trim the data by filtering it using a start and end date. If it were not for other views getting the information from this view/proceedure I would have just built it in FPMI. So what I would like to do if possible is send over the variables startdate and enddate to the stored proceedure.
So how does one go about doing this if its even possible? Thanks and have a great day.
Hi mrtweaver,
This is similar to what I am trying to do, so I thought I could perhaps provide some measure of assistance…
For your stored procedure you just need to add two variables for your start and end dates like this: [code]CREATE PROCEDURE procMyMassiveTableByDateSelect
@start DATETIME, --Earliest cutoff
@end DATETIME --Latest cutoff
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM MyMassiveTable
WHERE StartTime >= @start
AND StopTime <= @end
END[/code]
Now this next part I’m not 100% sure on since I am really new to FSQL…
You need to create an action item which will call your stored procedure. You can have the action item call it either by an expression or just raw sql. I’m not sure how to do it via an expression, but the raw sql way would be something like this:SELECT dbo.procMyMassiveTableByDateSelect(ToDate(Concat({start_year},"-",{start_month},"-",{start_day}," ",{start_hour},":",{start_minute},":",{start_second})), ToDate(Concat({end_year},"-",{end_month},"-",{end_day}," ",{end_hour},":",{end_minute},":",{end_second})));
And then have that action item map it’s results to whatever item you want.
Hope that helps you out some.
–HandledException
HandledException’s post is more complete, but I thought I’d chime in, you should be able to run a query with EXEC as well.
EXEC myStoredProc '2009-8-10', '2009-8-13'