Sql-server – SQL Server 2000 DTS package, filter rows using stored procedure returned date

You need to split your parameters into rows and use where ... in as a check.

Using a split function like this one your code could look like this.

CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @ProjHomeGrp NVARCHAR(MAX) ,
    @ProjHier NVARCHAR(MAX)
AS 
BEGIN

    DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))

    INSERT INTO @ProjHomeGrpTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHomeGrp);

    DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))

    INSERT INTO @ProjHierTbl(Value)
    SELECT LTRIM(RTRIM(s))
    FROM dbo.Split(',', @ProjHier);

    SELECT  [Capital Project] ,
            [Capital Task] ,
            ResourceName ,
            ProjectName ,
            [Project Home Group] ,
            ActualWork ,
            TimeByDay ,
            ResourceStandardRate ,
            ActualWork * ResourceStandardRate AS Dollars ,
            [Project Hierarchy]
    FROM    [IR.CapOnly]
    WHERE   ( TimeByDay >= @StartDate )
            AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
            AND ( TimeByDay <= @EndDate )
            AND ( ActualWork > 0 )
            AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
    ORDER BY ProjectName ,
            ResourceName

END 

For an ad hoc batch, you need to type the date values and assign them to the variables:

DECLARE @fromperiod varchar(8),
        @toperiod   varchar(8);

SELECT @fromperiod = '20130101',
       @toperiod = '20130131';

An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the toolbar, and then editing the values in the Locals tab, but this seems to be a pretty cumbersome way to avoid a little typing:

There are probably also ways you could use templates or snippets to have a slightly easier way to input values, but I don't find the above that much of a blocker in the first place.

For Reporting Services, I know there are ways to allow users to enter data and have those passed at parameters, but someone else is going to have to give you the skinny on that (the documentation might be helpful as well). I can spell Reporting Services but not much more than that. If this is a stored procedure it probably makes things a lot easier.