求助：如何通过SQL查询向含geography类型的存储过程传值？

Hey there! I totally get the frustration of spending hours troubleshooting a syntax error—let's get this sorted out for you.

The issue here is that you're using PostgreSQL-style type casting with ::, but SQL Server uses a different approach to work with geography data. That's exactly why you're seeing the "Incorrect syntax near '::'" error.

First, let's recap the correct way to pass geography values to your stored procedure in SQL Server. Let's assume your stored procedure looks something like this (adjust to match your actual definition if needed):

CREATE PROCEDURE AddNewBranch
    @BranchName NVARCHAR(100),
    @BranchAddress NVARCHAR(255),
    @BranchLocation GEOGRAPHY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Branches (Name, Address, Location)
    VALUES (@BranchName, @BranchAddress, @BranchLocation);
END

Correct Ways to Pass Geography Data #

You have two common, reliable methods to construct a geography object for your procedure call:

1. Use GEOGRAPHY::Point() for Latitude/Longitude Coordinates

If you have explicit latitude and longitude values, this is the most straightforward method. The syntax is GEOGRAPHY::Point(Latitude, Longitude, SRID)—where SRID 4326 is the standard WGS84 coordinate system used by most mapping tools.

Example call:

EXEC AddNewBranch
    @BranchName = 'Downtown Central',
    @BranchAddress = '789 Pine St, Metro City',
    @BranchLocation = GEOGRAPHY::Point(34.0522, -118.2437, 4326);

2. Use GEOGRAPHY::STGeomFromText() for WKT (Well-Known Text)

If you're working with Well-Known Text format (like POINT(), POLYGON(), etc.), use this method to convert the text to a geography object.

Example call:

EXEC AddNewBranch
    @BranchName = 'Westside Branch',
    @BranchAddress = '321 Cedar Rd, Suburb Town',
    @BranchLocation = GEOGRAPHY::STGeomFromText('POINT(34.0522 -118.2437)', 4326);

Key Notes to Avoid Issues #

• Match SRIDs: Make sure the SRID you use when constructing the geography object matches the SRID defined for the Location column in your Branches table. Mismatched SRIDs will cause errors when inserting.
• Forget the :: for Casting: In SQL Server, :: is used to access static methods of system types (like GEOGRAPHY::Point), not for type casting like in PostgreSQL. So avoid syntax like ::geography('POINT(...)') entirely.

That should fix your syntax error and let you successfully pass geography data to your stored procedure. Let me know if you run into any other hiccups!

内容的提问来源于stack exchange，提问作者Tomer Halaf