CODEWITHSUNDEEP
sqlazurecursorscalargeography
IrfanClemson
IrfanClemson

Reputation: 1779

Azure SQL Server, Geographic Data and Scalar Variable Error

I have a dedicated Azure SQL environment where I am using a loop to get US States names like 'AB' or 'NY' etc and also geographical boundaries from the same table. Code and error below:

DECLARE db_cursor CURSOR
            FOR SELECT STUSPS, State_Boundaries_geog FROM [dbo].[gis_US_States_GeoJSON]
DECLARE @query VARCHAR(max)
DECLARE @dbname VARCHAR(100)
DECLARE @target_state CHAR(4) 
DECLARE @geom_state geography
DECLARE @distance FLOAT = 482803; -- A distance in metres   482803 = 300 miles
OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @target_state, @geom_state;
WHILE @@FETCH_STATUS = 0
BEGIN
    --PRINT @geom_state.STAsText() --prints correctly
    SET @query ='INSERT INTO dbo.gis_shell300_imls(MID)(SELECT m.MID FROM dbo.Museums m , dbo.zcta z WHERE @geom_state.STContains(z.ZCTA_Centroid_Geography)  = 1)'            
   EXECUTE(@query) -- Generates the  'Must declare the scalar variable "@geom_state" error  
   FETCH NEXT FROM db_cursor INTO @target_state, @geom_state
END
CLOSE db_cursor 
DEALLOCATE db_cursor

The above code generates error about scalar variable @geom_state as shown above. Now, if I change to something like:

SET @query ='INSERT INTO dbo.gis_shell300_imls (MID)(SELECT m.MID FROM dbo.Museums m , dbo.zcta z WHERE'+ @geom_state+'.STContains(z.ZCTA_Centroid_Geography)  = 1)'

Note: Using the + operator where now the @geom_state variable is recognized, I get this error:

Invalid operator for data type. Operator equals add, type equals geography.

How do I fix it? I have looked into some string concatenations but haven't found a solution yet.

Thanks!

Upvotes: 0

Views: 26

Answers (1)

IrfanClemson
IrfanClemson

Reputation: 1779

This is working for me and it is based on modified version of this:

Is there a way to loop through a table variable in TSQL without using a cursor?

As you can see, no more Cursors! HTH someone!

DECLARE @target_state CHAR(4) 
DECLARE @geom_state geography
DECLARE @distance FLOAT = 482803; -- A distance in metres   482803 = 300 miles
declare @RowNum int, @State_ID int
select State_ID=MAX(State_ID) FROM [dbo].[gis_US_States_GeoJSON]     --start with the highest ID
Select @RowNum = Count(*) From [dbo].[gis_US_States_GeoJSON]       --get total number of records
WHILE @RowNum > 0                          --loop until no more records
BEGIN   
    select  @target_state = STUSPS, @geom_state = State_Boundaries_geog
    from [dbo].[US_States] where State_ID= @RowNum 
    INSERT INTO dbo.target_table(MID)(SELECT m.MID 
       FROM dbo.MuseumFile m , dbo.zcta_national z
      WHERE
       @geom_state.STContains(z.ZCTA_Centroid_Geography)  = 1)
    set @RowNum = @RowNum - 1    --decrease count
END

Upvotes: 0

Related Questions