I'm working on two DTS Packages. One exports 30000 records what meet criteria and the second exports all. The problem I have is that I need to control them with an if/else statement in this Stored Procedure and I don't know how.
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @OfficeID varchar(50)
DECLARE @path VARCHAR(8000)
DECLARE @id INT
DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND (enddate >= GETDATE() OR enddate IS NULL)
OPEN mdb_list
FETCH FIRST FROM mdb_list
INTO @OfficeID, @path
WHILE @@FETCH_STATUS = 0
BEGIN
IF @path IS NOT NULL AND @path <> ''
BEGIN
*****************
Here's where I need help....I need to check for records in tbl_template that match certain criteria. If there are at least X number of records, I need to run one DTS Package. If there are not at least X number of records, I need to run another.
*****************
END
FETCH NEXT FROM mdb_list
INTO @OfficeID, @path
END
CLOSE mdb_list
DEALLOCATE mdb_list
GO
Yes...
& Yes The will be dropped automatically on exit from the procedure....
>>OK, the update to tbl_sf3Lists actually happens in the exportLists package.
>>I'm only using the select statement in this context to get the greatest listID and that value gets passed to through the call for that package.
so each invocation will have to retrieve the latest value... it needs to go in the while loop
>>So at this point, I think the following would work
no more like this
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + OfficeID + '" '
Else '"resetTemplate" /A Officeid="' + officeid + '"'
end as cmd
,Identity(int,1,1) as rowno
,phones
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
-- it looks like you can use Max to determine the listid........ which is preferable to the TOP 1 ... Desc code
-- but if i'm wrong then replace it with your code...
Select @script='Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phones>2500
then '/A ExportID="' + convert(varchar(10),(Selec
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
-- invoke @script as you desire
End
Return
hth