Cursor statement / if else condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Cursor statement / if else condition

Author

Topic

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 12:34:21

I have the following cursor heading where I am looking at a certain line of business, in this case 'Public Risk MTPP'


DECLARE c1 CURSOR global READ_ONLY FOR

select RptLOB 
from AnalyticsV2P7.CapModel.RptLineOfBusiness
where RptLOB = 'Public Risk MTPP'

OPEN c1

FETCH NEXT FROM c1 INTO @LOB

Within the cursor, I have an if else condition like so:


if @LOB = 'Property'
begin
  
    **Select query here**
end
else if @LOB = 'Public Risk MTPP'
begin
  
    **Select query here**
end

When I run it for 'Public Risk MTPP', it doesn't work. However if I change the name of the RptLOB to say 'Energy' both in my cursor heading and in my if else condition, it works. 'Public Risk MTPP' exists in the table and I've made sure the spelling is correct.

Any idea as to what can be causing this issue? The only difference between these lines that I can find is the spacing in the naming.

Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 12:41:20

First of all, why are you using a cursor? There are very, very few cases (if, indeed, there are any at all) where a cursor cannot be replaced by normal set logic and I don't think that your example is one of them.

Second, obviously 'Public Risk MTPP' can be found in the Energy column but not in the RptLOB column.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 12:48:10

You're right I don't have to use a cursor but I'm actually doing this for more lines than just Public Risk MTPP. (I've used a cursor in other instances that were VERY helpful indeed). There's only one column and that is RptLOB. Energy and Public Risk MTPP are both in that column as well as many others. I was wondering why it's reading Energy but not Public Risk MTPP.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 12:55:10

The reason I'm using a cursor is because for about 7 lines (and eventually it will be more), I need to export into a csv file. I don't want to create 7 or more tables with 25 columns over and over. I'm overwriting on 1 table right now by using the cursor.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 13:12:35

"Energy and Public Risk MTPP are both in that column "

Can't be. Post a row of each.

"I don't want to create 7 or more tables with 25 columns over and over. "

Well, neither would I! But I'd find a way to do it set-based. Cursors are performance killers. I keep telling my boss, "If you every find me writing a cursor, fire me!" So far, I still have a job.

Post the code you will use to do the export and let's see if we can find a better way.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 13:29:58

lol I'm not using SQL on a daily basis so performance is not that big of an issue. I'm simply replacing Access queries one of my coworkers wrote to procedures in SQL.

RptLOB
Energy
Public Risk MTPP

Code:

select RptLOB
from AnalyticsV2P7.CapModel.RptLineOfBusiness
where RptLOB in ('Energy', 'Public Risk MTPP')

Full Precedure:
I'm currently inputing certain select queries in a table, this one into dbo.DFP_Loss.
Then I was inserting dbo.DFP_Loss into my csv file (there's a section of wording at the top of the csv file and then the output of the query)

set @DFP = 'select * from dbo.DFP_Loss
order by Cov, [Claim#], AYDate, EvalDate'
insert into dbo.Loss_csv --this is my csv table that is being overwritten
execute sp_executesql @DFP

--Insert into csv file using shell command (there's more to it obviously)
execute xp_cmdshell

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 14:08:29

Where is the variable @LOB set?

Does this pseudo-code capture the logic:

For each row in AnalyticsV2P7.CapModel.RptLineOfBusiness
if RptLOB = 'Public Risk MTPP'
Delete all rows from dbo.DFP_Loss
Populate dbo.DFP_Loss from your queries
Write dbo.DFP_Loss to a CSV file

????

I'd probably use SSIS to do this job. It's kind of its bread and butter.

If you want to stick with a proc or script, though, you clearly need some kind of loop. How many rows do you expect to be returned from the query in the cursor definition? It it's not too many, the cursor won't hurt the performance enough to matter. OTOH you can do it in different ways...

e.g.


declare @t table (id int identity(1,1) primary key, data varchar(50))
insert into @t(data)
   select * from (values ('a'),('b'),('c')) v(data) -- AnalyticsV2P7.CapModel.RptLineOfBusiness in your case
   -- your WHERE clause

declare @rc int = @@ROWCOUNT
declare @i int = 1

while @i <= @rc begin
	select id, data from @t
	where id = @i 
        -- do something with the data; truncate your temp table; write your csv file, etc.
	set @i += 1
end

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 14:17:55

Currently @LOB is being used in my if condition so

if @LOB = 'DFP'
begin

queries

end

dbo.DFP_Loss has nothing to do with Public Risk, I just put it as an example..it's basically another line of business, so DFP, Energy, Public Risk MTPP..they're all the same. For all of these lines, i'm creating different select queries. I was just trying to use the cursor so I didn't have to create all those tables.

Also the rows are always going to be different depending on the line but most likely less than 1000.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 14:29:20

I can see where @LOB is being used; what I can't see is where it is being set.

Less than 1000 rows? So, 1000 csv files being generated? Well that's where most of the time and IO will be spent. Performance wise, you can do it with a cursor or without. I'd opt for without (so my boss will let me keep my job!) Actually, I'd do it with SSIS.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 14:38:19

DECLARE c1 CURSOR global READ_ONLY FOR
select RptLOB
from AnalyticsV2P7.CapModel.RptLineOfBusiness
where RptLOB in ('DFP','Energy') --etc
OPEN c1

FETCH NEXT FROM c1 INTO @LOB

not 1000 csv's. I'm creating a csv with a specific heading and then inputting all the rows from my select query, which is what this code is doing:

declare @DFP nvarchar(max)
set @DFP = 'select * from dbo.DFP_Loss
order by Cov, [Claim#], AYDate, EvalDate'
insert into dbo.Loss_csv --this is my csv table that is being overwritten
execute sp_executesql @DFP

So 1 csv for each line of business...about 7 total.

I can play around with it, maybe do the loop instead.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 14:57:13

the while loop has the advantage of NOT holding a read lock on AnalyticsV2P7.CapModel.RptLineOfBusiness, since that is a one-and-done up front.

Tell me, the various queries you will put inside the IF/ELSE statement, do they result in the same number/type of columns. If so you can do this:


select ...
where @LOB = 'lob1'
UNION ALL
select ...
WHERE @LOB = 'lob2'
...

Also, if the individual queries are not dynamic, you could use a big WITH statement:


WITH lob1 as (
    SELECT ...
    ),
    lob2 as (...
    )

select * from lob1 union all select * from lob2 ...

The execution plan will look like SQL reads all tables in the UNION, but actually it doesn't. At execution time, it skips those where the WHERE clause is not satisfied, and since it's against a variable, this is evaluated up front.

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 15:07:16

Yeah I use cte's all the time but because I have to use dynamic SQL to create a csv file, I gave up on that.

Also, yes, all the columns are exactly the same. The thing is I'm not union-ing all of them. As soon as I have the needed select query for one line of business, I create the dynamic sql to input into the csv file (as the csv files for each line of business are called something else), and then move on to the next line of business.

I'm messing around with the loop. ALMOST works perfectly...for some reason it's giving me a hard time on one line lol

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 15:11:08

Why not do it in SSIS? Probably much simpler and more robust and potentially faster. SSIS can do all your CSV files in parallel if you set it up correctly

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 15:15:03

Because I've never used it before :X

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-14 : 15:16:01

Good answer! Opportunity to learn? Be the hero!!

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2014-08-14 : 15:31:46

It definitely is!