September 25, 2013 at 1:56 pm
DECLARE @County NVARCHAR(100)
DECLARE @Longitude decimal(19,15) = -87.979529
DECLARE @Latitude decimal(19,15) = 42.219469
DECLARE @sqlquery AS NVARCHAR(500)
DECLARE @LongitudeLatitude geography
SET @sqlquery = N'SET @LongitudeLatitude = geography::Parse(''POINT(' + cast(@Longitude as varchar) + ' ' + cast(@Latitude as varchar) + ')'')'
-- SET @LongitudeLatitude = geography::Parse('POINT(-87.979529000000000 42.219469000000000)')
print @sqlquery
exec sp_executesql @sqlquery
Gives me
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@LongitudeLatitude".
What could be the issue?
hello
September 25, 2013 at 2:13 pm
The problem is that you are declaring your variables outside the dynamic sql. sp_executesql changes the execution context so variables declared outside the sql string are not available in the sql string.
I don't see a reason to use dynamic SQL. Why not just do this:
DECLARE @County NVARCHAR(100)
DECLARE @Longitude decimal(19,15) = -87.979529
DECLARE @Latitude decimal(19,15) = 42.219469
DECLARE @sqlquery AS NVARCHAR(500)
DECLARE @LongitudeLatitude geography
SET @LongitudeLatitude = geography::Parse('POINT(' + cast(@Longitude as varchar) + ' ' + cast(@Latitude as varchar) + ')')
SELECT @LongitudeLatitude
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 25, 2013 at 2:31 pm
Thank you.
hello
September 25, 2013 at 9:26 pm
To avoid messing around with varchars altogether you could do
SET @LongitudeLatitude = Geography::Point(@Latitude,@Longitude,4326)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply