How to modify these procedure for my input is null or zero

Question

Post reply

How to modify these procedure for my input is null or zero

raghuldrag

Hall of Fame

Points: 3160
More actions
April 30, 2014 at 4:46 am

#307099

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AlexSQLForums SSChampion Points: 14277 More actions · Answer 1

I fixed your sample code and added default parameter into the sproc.

CREATE TABLE ind_state (

ind_stat_id INT

,ind_state_name VARCHAR(50)

)

INSERT INTO ind_state

VALUES (

'1'

,'Pondi'

)

,(

'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'

)

,(

'2'

,'KaraiKal'

,'1'

)

,(

'3'

,'Chennai'

,'2'

)

,(

'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'

)

,(

'654012'

,'24.230'

,'75.1462'

,'2'

)

,(

'609609'

,'10.916700000000001'

,'79.816699999999997 '

,'1'

)

CREATE TABLE ind_cust_det (

pincode INT

,cus_id VARCHAR(50)

,sales INT

)

INSERT INTO ind_cust_det

VALUES (

'600004'

,'509'

,600.0

)

,(

'609609'

,'522'

,400

)

GO

ALTER PROCEDURE LAT_TEST_DYNAM0 @dist_id INT = 2

AS

BEGIN

CREATE TABLE #temp (

pincode VARCHAR(10)

,latitude NUMERIC(18, 10)

,longitude NUMERIC(18, 10)

,descp VARCHAR(5000)

)

IF (@dist_id IS NOT NULL)

OR (LEN(@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 @cus_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 cus_id

,sum(sales) sales

FROM ind_cust_det(NOLOCK)

WHERE pincode = @pin

GROUP BY cus_id

OPEN cur1

FETCH first

FROM cur1

INTO @cus_id

,@sales

WHILE @@fetch_status = 0

BEGIN

SET @des = @des + '<tr><td align="center">' + convert(VARCHAR, @cus_id) + '</td><td align="center">' + convert(VARCHAR, @sales) + '</td></tr>'

SET @total = @total + @sales

FETCH NEXT

FROM cur1

INTO @cus_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

Alex S

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

WOW!!! I hope your users don't mind going for lunch when they fire this thing off. You have nested cursors. Even worse is your inner cursor is recreated for every iteration of the outer cursor. :w00t::w00t::w00t::w00t::w00t:

What are trying to accomplish here. I don't think you need a cursor at all but I am not exactly sure what the desired result is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I didn't even notice the NOLOCK hints littered throughout that. Do you know what that hint does? Are the users ok with missing and/or duplicated data occasionally when this runs?

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

It took me a while to go through your code to figure out what it was doing.

As stated before, you just need to add a default to your parameter. But you should do a lot more than that. As Sean said, this might take a while to run due to the nested cursors.

Here's a version that will handle the default value with no problem.

alter procedure LAT_TEST_DYNAM0

@dist_id int = 0

as

begin

CREATE TABLE #temp(

pincode VARCHAR(10)

,latitude NUMERIC(18, 10)

,longitude NUMERIC(18, 10)

,descp VARCHAR(5000)

)

INSERT INTO #temp

select CAST( g.pincode AS varchar(10)),g.latitude,g.longitude,

'<table border="1"><tr><th>Customer</th><th>Sales</th></tr>'

+ (SELECT '<tr><td align="center">' + convert(VARCHAR(10), cust_id)

+ '</td><td align="center">' + convert(VARCHAR(18), SUM(Sales)) + '</td></tr>'

FROM ind_cust_det i

WHERE i.pincode = g.pincode

GROUP BY cust_id

FOR XML PATH(''),TYPE).value('.','varchar(max)')

+ '<tr><td align="center">TOTAL</td><td>' + convert(VARCHAR(18), SUM(sales)) + '</td></tr></table>'

from ind_cust_det c

inner join ind_geo_loc g on c.pincode=g.pincode

where g.ind_dist_id = @dist_id

GROUP BY g.pincode,g.latitude,g.longitude

UNION ALL

SELECT CAST('00911' AS varchar(10))

,'20.593684'

,'78.962880'

,'Amrutanjan Area Wise Sales'

WHERE ISNULL(@dist_id, 0) = 0

SELECT * FROM #temp

end

You might not even need the temp table, I just left it there to keep the data types you were using.

Feel free to ask any questions that you have.

Reference: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

PS. Please check your code before posting. It was full of errors.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2