Solved

If,Then,Else with Stored Procedure

Posted on 2004-04-02
22
1,865 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:1cell
22 Comments
 
LVL 6

Expert Comment

by:acampoma
ID: 10742518
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
0
 
LVL 6

Author Comment

by:1cell
ID: 10742636
being as tbl_template could contain literally millions of qualified records is there a way to have it not count more than X#?
0
 
LVL 6

Author Comment

by:1cell
ID: 10742662
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10742842
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
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 10742894
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#
0
 
LVL 6

Author Comment

by:1cell
ID: 10743437
"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.

0
 
LVL 6

Author Comment

by:1cell
ID: 10744365
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10745487
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

0
 
LVL 6

Author Comment

by:1cell
ID: 10758960
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???




0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10759217
>>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
0
 
LVL 6

Author Comment

by:1cell
ID: 10759791
"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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
ID: 10759899
>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????
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),(Select @exportid=coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
                            else ' ' end
                 , @id=@id+1
     from #temp
     Where rowno = @id  
  -- invoke @script as you desire
End
 
Return

hth
0
 
LVL 6

Author Comment

by:1cell
ID: 10760588
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...

0
 
LVL 6

Author Comment

by:1cell
ID: 10760964
lol, nevermind i got it. I need to keep my languages straight. thanks for all the help.
0
 
LVL 6

Author Comment

by:1cell
ID: 10778001
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
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10778609
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
0
 
LVL 6

Author Comment

by:1cell
ID: 10778873
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.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10790144
try
changing  + officeid
to
 + convert(varchar(10),officeid)

hth
0
 
LVL 6

Author Comment

by:1cell
ID: 10808321
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.
0
 
LVL 6

Author Comment

by:1cell
ID: 10808589
I think it's a problem with my DTS Package.  I'll post back if I can't figure it out.
0
 
LVL 6

Author Comment

by:1cell
ID: 10817427
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.
0
 
LVL 6

Author Comment

by:1cell
ID: 10941811
Lowfatspread, any feedback would really be helpful.  I haven't been able to figure out the problem with this.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now