-- =============================================
-- Author: Jayendrasinh Gohil
-- ============================================
ALTER PROCEDURE [dbo].[GetDistrictSales]
(
@v_i_Stateid int
)
AS
BEGIN
-- Create Local Temp Table
CREATE TABLE #temp_distict
(
stateid INT,
state VARCHAR(150) NULL,
districtid INT,
district VARCHAR(150) NULL,
geom GEOMETRY NULL,
sales NUMERIC(18, 0) NULL DEFAULT 0
)
-- Insert Data in to Local Temp Table.
INSERT INTO #temp_distict
(
stateid,
state,
districtid,
district,
geom,
sales
)
SELECT a.[id_1],
a.[name_1] AS State,
a.[id_2],
a.[name_2] AS Distict,
a.geom AS Geom,
0 AS sales
FROM dbo.ind_adm2 AS a
WHERE a.[id_1] = @v_i_Stateid
-- Variable Declaration for Cursor
DECLARE @Stateid INT,
@Districtid INT,
@Sales NUMERIC(18, 0)
-- Declare Cursor for Query
DECLARE sales_cursor CURSOR FOR
SELECT
d.[stateid],
d.[districtid],
d.[sales]
FROM [dbo].[districtsales] AS d
WHERE d.[stateid] = @v_i_Stateid
OPEN sales_cursor -- Open Cursor
FETCH next FROM sales_cursor INTO @Stateid, @Districtid, @Sales
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #temp_distict
SET sales = @Sales
WHERE stateid = @Stateid
AND districtid = @Districtid
FETCH next FROM sales_cursor
INTO @Stateid, @Districtid, @Sales;
END;
CLOSE sales_cursor; -- Cloase Cursor
DEALLOCATE sales_cursor;
SELECT * FROM #temp_distict -- Select Data From Local Temp Table.
DROP TABLE #temp_distict -- Drop Local Table.
END