If,Then,Else with Stored Procedure

Declare a variable to hold your count
selelect @MyCount=Count(*) from blah, where blah

if @Mycount > somevalue Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D3197083E282980D6FD91AE6120D0B8AB7E610B969BCB9753DC630925ED7D5DE9CE9B960C53788CA2B4B93EE60BC26957C00AB5F2CD9A01E7F0D69AA0121B06EA7DE6B05E5E2E4AA06718F395465477695CF5BA6AFAC477596F582AF19B3D2F600E00B539E37AA5231D3362227CBB99F47CC21040DF1F23BB11A8FF3A6E726974F53C8202D06FB0129005F4A060A0956B88DD ')End
else Begin
dbo.xp_cmdshell('DTSRun /~Z0x050D69F27E2B010D8D3197083E282980D6FD91AE6120D0B8AB7E610B969BCB9753DC630925ED7D5DE9CE9B960C53788CA2B4B93EE60BC26957C00AB5F2CD9A01E7F0D69AA0121B06EA7DE6B05E5E2E4AA06718F395465477695CF5BA6AFAC477596F582AF19B3D2F600E00B539E37AA5231D3362227CBB99F47CC21040DF1F23BB11A8FF3A6E726974F53C8202D06FB0129005F4A060A0956B88DD ')End

being as tbl_template could contain literally millions of qualified records is there a way to have it not count more than X#?

I just don't want to spend the resources sifting through millions of records when I only need to know if there's, say 2500.

you can alter your cursor  to at least only select  "valid" listpaths...


DECLARE mdb_list CURSOR LOCAL SCROLL STATIC FOR
SELECT officeID, listpath
FROM tbl_office
WHERE startdate <= GETDATE() AND
Getdate() <= coalesce(enddate,Getdate())
and coalesce(listpath,'') <> ''

OPEN mdb_list

FETCH FIRST FROM mdb_list
INTO @OfficeID, @path

WHILE @@FETCH_STATUS = 0
BEGIN
--IF @path IS NOT NULL AND @path <> ''
--BEGIN



there is no "real" way to limit the number of records you need sql server to count....

you could try

select  count(*) from
 (select top 2501 * from blah
      where blah
        order by blah ) as x
having count(*) > 2500


but that wont necessarily stop SQL server having to process every row to get a 2501 subset...

hth

1 .you can use nested cursor:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp
------------------------------------------------------------
2 .>is there a way to have it not count more than X#?

set rowcount x#

"but that wont necessarily stop SQL server having to process every row to get a 2501 subset..."

I think that's my problem anyway.  I mean, what if it DOES have to look through 10,000,000 to find if there's 2500 that meet the criteria.  

SO, it's just a performance issue I'll have to plan for.

I think that'll do it, ignoring the performance issue, what I've got is this.  Anybody see any problems or have any further comments?

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

DECLARE @MyCount int
select @MyCount=Count(distinct homephone) from tbl_template where regionID = @OfficeID and exportID is null

if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
      '/A MDBFile="' + @path + '" '
END

ELSE
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "resetTemplate" ' +
      '/A OfficeID="' + @OfficeID +'"'
END

END
   FETCH NEXT FROM mdb_list
   INTO @OfficeID, @path

END
CLOSE mdb_list
DEALLOCATE mdb_list
GO

OK you don't need the cursors

and i believe that this will be much more efficient


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 + '" '
            Else '"resetTemplate" /A Officeid="' + officeid + '"'  
            end as cmd
      ,Identity(int,1,1) as rowno
  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
   Select @script='Dtsrun  /S "' + @@SERVERNAME + '" /E /N ' + cmd
         , @id=@id+1
     from #temp
     Where rowno = @id  
  -- invoke @script as you desire
End
 
Return

Thanks lowfatspread.  I can see what you're doing there but I'm an idiot so I probably wont understand for at least an hour.

so, first, if you wouldn't mind elaborating on the reasons that's more efficient, I'm sure the information wouldn't hurt me.  
And forgive me for being stupid but what does "into #phone" do?  Is it some kind of variable???  Sorry, I'm obviously new to this...

Second, I actually changed a part on Friday.
--old
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
      '/A MDBFile="' + @path + '" '
END

ELSE

--new
if @MyCount > 2500
BEGIN
SELECT @script = 'dtsrun /S "' + @@SERVERNAME + '" /E /N "exportLists" ' +
     '/A MDBFile="' + @path + '" ' +
     '/A OfficeID="' + @OfficeID + '" ' +
      '/A ExportID="' + @ExportID + '" '
END
ELSE

END



So I'm assuming that I'd do something like...

Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + @OfficeID + '" ' +  '/A ExportID="' + @ExportID + '" '
 Else '"resetTemplate" /A Officeid="' + officeid + '"'  
            end as cmd
      ,Identity(int,1,1) as rowno
  into #temp
from #office as O        
inner Join #phone as p
   on o.officeid=p.regionid




And finally I also have to add another query to actually get the correct exportID.

Select top 1 listid from tbl_sf3Lists order by listID desc

What's the best way to get the results+1 into @ExportID???

>>so, first, if you wouldn't mind elaborating on the reasons that's more efficient, I'm sure the information wouldn't hurt me.  
>>And forgive me for being stupid but what does "into #phone" do?  Is it some kind of variable???  Sorry, I'm obviously new to this...


well MS SQL Server Cursors are very slow compared to allowing it to perform set based operations is the basic reason...

select ... into #phone   from ....

this is a  ms sql server SQL Extension  which allows you to create a table through the select statement without
having to pre define it with a create table statement and then insert into it...
 since the table name begins with a #   it is a TEMPORARY TABLE

finally the code hopefully allows for the minimum number of passes of the source tables , in the most efficient manner
available to the Database Engine...

change this
Select case when phones > 2500 then '"exportLists" /A MDBFile="' + listpath + '" '
            Else '"resetTemplate" /A Officeid="' + officeid + '"'  
            end as cmd
      ,Identity(int,1,1) as rowno
  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
   Select @script='Dtsrun  /S "' + @@SERVERNAME + '" /E /N ' + cmd
         , @id=@id+1
     from #temp
     Where rowno = @id  
  -- invoke @script as you desire
End

to
Declare @exportid int
Select @exportid=coalesce(Max(listid),0) + 1 from tbl_sf3Lists

/*
-- you don't make it clear how tbl_sf3lists is maintained ...  
Update tbl_sf3lists
 set listid=listid + coalesce((select count(*) from #temp where phones>2500),0)
*/



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
   Select @script='Dtsrun  /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then  '/A ExportID="' + convert(varchar(10),@exportid) + '" ' else ' ' end
         ,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
         , @id=@id+1
     from #temp
     Where rowno = @id  
  -- invoke @script as you desire
End


hth

"well MS SQL Server Cursors are very slow compared to allowing it to perform set based operations is the basic reason..."

So essentially we're creating temp tables and querying them.  Is there any kind of cleanup required for the temp tables or do they get disposed of naturally????


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 at this point, I think the following would work


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 top 1 listid
 into @exportID
 from tbl_sf3Lists
 order by listID desc

Declare @exportid int
Select @exportid=coalesce(Max(listid),0) + 1 from tbl_sf3Lists

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
   Select @script='Dtsrun  /S "' + @@SERVERNAME + '" /E /N ' + cmd + case when phones>2500 then  '/A ExportID="' + convert(varchar(10),@exportid) + '" ' else ' ' end
         ,@exportid = @exportid + case when phones > 2500 then 1 else 0 end
         , @id=@id+1
     from #temp
     Where rowno = @id  
  -- invoke @script as you desire
End

 
Return

I'm pretty sure that takes care of it.  
I haven't been able to test it yet because of a problem with one of my activeX scripts in the DTS package but the logic is there.

It would sure help if you've got any idea why...

Function Main()
  Dim task
  Set task = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask  
  task.SQLStatement = "insert into tbl_sf3lists (listname, totalRecords, availableRecords, officeID, createDate, notes) VALUES ('" + DTSGlobalVariables("exportID") + Date() + "', 30000,30000," + DTSGlobalVariables("OfficeID") + "," + Date() + ",'Auto Export')"
Main = DTSTaskExecResult_Success
End Function

Gets an error: Type Mismatch: '[string: "insert into tbl_sf3l"]'

I'm sure it's my syntax somewhere...

lol, nevermind i got it. I need to keep my languages straight. thanks for all the help.

Hey, hope you're still around, finally got around to testing this and I'm getting an error I hope you can help me with...

Error 170: Line 40: Incorrect Syntax near '='.

then  '/A ExportID="' + convert(varchar(10),(Select @exportid=coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '

This is what I've got...

CREATE PROCEDURE exportLists
AS

DECLARE @script    VARCHAR(8000)
DECLARE @id        INT
DECLARE @max       INT
DECLARE @exportID  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
   Select @script='"Dtsrun  /S "' + @@SERVERNAME + '" /E /N ' + cmd
               + case when phones>2500
                          then  '/A ExportID="' + convert(varchar(10),(Select @exportid=coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
                            else ' ' end
                 , @id=@id+1
     from #temp
     Where rowno = @id  
End
 
Return

sorry try

then  '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '

and you can probably remove any reference to @exportid ...

hth

Had to change the phones(es) to phone(s).

ie.  phones>2500 = phone > 2500

If that's correct then I've got one more error it seems

Server: Msg 245, Level 16, State 1, Procedure exportLists, Line 22
Syntax error converting the varchar value '"resetTemplate" /A Officeid="' to a column of data type int.

try
changing  + officeid
to
 + convert(varchar(10),officeid)

hth

Thanks for replying.  I haven't been able to figure this one out.

well, the error stopped but it still doesn't seem to be executing the DTS Packages.

I just get

(3 row(s) affected)


(3 row(s) affected)


(3 row(s) affected)

but nothing else is done.
It's like it's looping through the stored procedure but not executing any of the DTS packages for eah loop.

I think it's a problem with my DTS Package.  I'll post back if I can't figure it out.

OK, There was a problem with the DTS Package but I fixed it.  
I had to add an ActiveX task to set the Access connection to the variable.  
Now it works when I populate the variables.

However, the Stored Procedure you helped me create still gets the same results...it doesn't execute the package.

Any help would really be appreciated.

Lowfatspread, any feedback would really be helpful.  I haven't been able to figure out the problem with this.