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.