Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to modify these procedure for my input is null or zero Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 4:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 11:33 PM
Points: 172, Visits: 427
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
Post #1566303
Posted Wednesday, April 30, 2014 9:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 10:42 AM
Points: 1,288, Visits: 1,861
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
Post #1566461
Posted Wednesday, April 30, 2014 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,250, Visits: 12,086
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.

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566463
Posted Wednesday, April 30, 2014 9:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:12 PM
Points: 13,250, Visits: 12,086
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/


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1566466
Posted Wednesday, April 30, 2014 10:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:46 PM
Points: 3,485, Visits: 7,526
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1566478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse