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