Stored Procedure - Union - Should or can I use a cursor

[cfcProgrammer]

Hi,
I am calling a stored procedure from an ASP page, in the stored procedure I have to run a select statement using a union to another select statement. What I thought I could do was create a view and then just query the view and send the data back to a result set and display the data back on the asp page. However, I've discovered that I can not create or alter a view within a stored procedure. I haven't heard anything good regarding cursors and am not even sure if I can get my results usning one..

Can anyone help me figure out the best solution to this issue.

here is the select statements that are giving me the correct results I just need to figure out how to accomplish this through a stored procedure.

Alter view FSHRS as
(select client1name as "Officer", sum(hours) as "Hours", count(mid) as "Mission"
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-apr-2004' and '31-mar-2005' and 
left(m.mid,4)like 'BKN%'
and fo1='Y' group by client1name)
Union
(select client2name as "Officer", sum(hours) as "Hours", count(mid) as "Missions"
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-apr-2004' and '31-mar-2005' and 
left(m.mid,4)like 'BKN%'
and fo2='Y' group by client2name)

SELECT distinct Officer as 'Officer', sum(Hours) as 'Hours', sum(Mission) as 'Missions'
from FSHRS
group by Officer

Where you see the hardcoding of dates and mid.. these will be passed in as parameters.

Thank you so much for any help that you can provide.

cfcProgrammer

 

[PWise]

why do you have to alter the view

pass the parameters to the Sp and the select statment


create procedure xxx

@StartDate datetime
@enddate Datetime

as

Select * from(
(select client1name as "Officer", sum(hours) as "Hours", count(mid) as "Mission"
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between @startDate and @enddate and
left(m.mid,4)like 'BKN%'
and fo1='Y' group by client1name)
Union
(select client2name as "Officer", sum(hours) as "Hours", count(mid) as "Missions"
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between @startDate and @enddate and
left(m.mid,4)like 'BKN%'
and fo2='Y' group by client2name))dt


run the sp like

exec xxx '2004/04/01' , '2005/05/01'

 

[cfcProgrammer]

I did try this earlier but I was getting an issue around the UNION statement but it was just syntax issues... thank you so much... there is one other question I have... how can I group the results... the two select statements group by client1 and client2.. i need the result to be grouped by the combination of these clients.

when I had created the view I could just select the single column "Officer" but I'm unsure how i can do that here.. I tried using the "Officer" but that didn't work...

any ideas...

Again thank you so much for your quick response and help.. believe me it is greatly appreciated.

cfcProgrammer

 

[PWise]

What result are you getting and what result do you want?

 

[cfcProgrammer]

right now I am not getting anything back from the stored procedure.. my record set is empty... when I run this in analyzer I am getting results though.

If I leave the query as it is.. can an ASP recordset even accept this result. Will "dt" have the results the same as if I ran the select statments? I'm a little confused as how the data will look, will I still be able to put the result into an array and display to a page?

To explain what I am looking for I will explain how the data is stored.

I have client 1 and client 2 in a file. These clients represent officers... the officer can be client 1 one day and client 2 another day..(1st officer/2nd officer) but what I am looking for is the total hours they worked.. it doesn't matter if they were 1st or 2nd.

So I created this sql to select all the client1 and then all the client2 using the Union and storing into a view.. then I queried the view selecting distinct "Officer" and sum the hours and missions.. giving me the correct result for each officers total hours and missions...

If i keep the stored procedure the way it is the result will not have the total for each officer..and I will not be able to group by the officer.

Am I explaining this ok.. or have I confused you???

I know I can create a table and then just clear each time the stored procedure is ran and then insert the new records and query on that.. but htat just seems to be too much processing and having an unnecessary table...

HELP????!!!! LOL



cfcProgrammer

 

[SQLSister]

will this do it?

select Officer, sum(Hours) as HourTotal, sum(Mission) as MissionTotal from
(select client1name as Officer, sum(hours) as Hours, count(mid) as Mission
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-apr-2004' and '31-mar-2005' and 
left(m.mid,4)like 'BKN%'
and fo1='Y' group by client1name
Union
select client2name , sum(hours) , count(mid) 
from flt f join mission m on f.msID=m.msID
where convert(datetime,(convert(varchar(2),f.mn) + '/' + convert(varchar(2), f.dy) + '/' + convert(varchar(4),f.yr)))
between '01-apr-2004' and '31-mar-2005' and 
left(m.mid,4)like 'BKN%'
and fo2='Y' group by client2name) a
group by Officer

No need for temp tables (donot under any circumstances create real tables or you will have problems.) or a view. Use a derived table instead. (Key to a derived table is that you must use an alias, in this case I called it "a".)

"NOTHING is more important in a database than integrity." ESquared