How to show default value where the procedure input is null or zero

Posted by Raghuldrag under Sql Server on 4/30/2014 | Points: 10 | Views : 3281 | Status : [Member] | Replies : 1
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




Responses

Posted by: Bandi on: 5/1/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
isnull(nullif(@dist_id,0), YourValue)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raghuldrag, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response