Hi Friends,
i m creating web application for state and dist wise map
i ve the tables like
create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')
values ('2','TamilNadu')
create table ind_state_dist
(
ind_dist_id int,
ind_dist_name varchar(100),
ind_stat_id int
)
insert into ind_state_dist values ('1','mahe','1')
values ('2','KaraiKal','1')
values ('3','Chennai','2')
values ('4','Salem','2')
create table ind_geo_loc
(
pincode int,
latitude float,
longitude float,
ind_dist_id int
)
insert into ind_geo_loc values ('600004','13.0656','80.267200000000003','2')
values ('654012','24.230','75.1462','2')
values('609609','10.916700000000001', '79.816699999999997 ','1')
create table ind_cust
create table ind_cust_det
(
pincode int,
cus_id varchar(50),
sales varchar(50)
)
insert into ind_cust_det
values ('600004','509', 600.0)
values ('609609',522,400)
my output is depends on the dist selection so made procedure
alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0
begin
insert into #temp(pincode,latitude,longitude)
select distinct a.pincode,b.latitude,b.longitude from
ind_cust_det a (nolock) inner join ind_geo_loc b on a.pincode=b.pincode
where
b.ind_dist_id=@dist_id
declare @pin int
declare cur cursor dynamic for select distinct pincode from #temp
declare @sales numeric (18,2)
declare @total numeric (18,2)
declare @des varchar(5000)
declare @cust_id int
open cur
fetch first from cur into @pin
while @@fetch_status=0
begin
set @des=''
set @total=0
set @des='<table border="1"><tr><th>Customer</th><th>Sales</th></tr>'
declare cur1 cursor dynamic for select cust_id,sum(sales) sales from ind_cust_det (nolock) where pincode=@pin group by cust_id
open cur1
fetch first from cur1 into @cust_id,@sales
while @@fetch_status=0
begin
set @des=@des+ '<tr><td align="center">' + convert(varchar,@cust_id) + '</td><td align="center">' + convert(varchar,@sales) +'</td></tr>'
set @total=@total+@sales
fetch next from cur1 into @cust_id,@sales
end
set @des=@des+ '<tr><td align="center">TOTAL</td><td>' + convert(varchar,@total)+ '</td></tr></table>'
close cur1
deallocate cur1
update #temp set descp=@des where pincode=@pin
fetch next from cur into @pin
end
close cur
deallocate cur
end
else
begin
insert into #temp(pincode,latitude,longitude,descp) values ('00911','20.593684','78.962880','Amrutanjan Area Wise Sales')
end
select * from #temp
drop table #temp
end
myself doubt is when @dist_id is null or "0" how to show default value ?
otherwise above my code is correct ah?
kindly do the needfull