SQL error while using MySQL geometry function

Question

ALTER PROCEDURE dbo.StoredProcedure1    
AS  
DECLARE @Seedid int, @data1 float, @data2 float, @g geometry  

DECLARE member_cursor CURSOR FOR  
SELECT id from test1 ;  

OPEN member_cursor;  

FETCH NEXT FROM member_cursor  
INTO @Seedid ;  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    select @data1 =data1 from test where id=@Seedid  
    select @data2 =data2 from test where id=@Seedid  
    update test1  
       set data4 = geometry::STGeomFromText('POINT (@data1 @data2)', 0)
        where id=@Seedid;  
    -- This is executed as long as the previous fetch succeeds.  
    FETCH NEXT FROM member_cursor  
    INTO @Seedid ;  
END  
RETURN  
CLOSE member_cursor;  
DEALLOCATE member_cursor;

I am getting the following error:

The statement has been terminated.
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.FormatException: 24141: A number is expected at position 13 of the input. The input has @data1.

score 1 · Answer 1 · edited Jun 23 '14 at 15:41

1

After some effort, I finally found the solution for this problem. Posting the solution if someone else encounter such problem again. In Geometry when I am passing @data1 and @data2, its those variable as @data1 only not its float value. So, What we can do is following:

select @data1 =data1 from test1 where id=@Seedid  
select @data2 =data2 from test1 where id=@Seedid  
Set @POINTSTR = 'POINT(' + CAST(@data1 AS varchar(32)) + ' ' + CAST(@data2 AS varchar(32)) + ')';  
update test1  set data4 = geometry::STGeomFromText(@POINTSTR, 4326) where id=@Seedid;

edited Jun 23 '14 at 15:41

Derek

1,104
13
35

answered Feb 12 '13 at 18:02

PhantomM

825
6
17
34

Thanks for posting this! I should have realized that I needed to convert the points (floats in my case) to text before passing them into the `...FromText()` function :). – Derek Jun 23 '14 at 16:41

SQL error while using MySQL geometry function

1 Answers1