CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresstored-functions
baliman
baliman

Reputation: 620

can not call function in stored procedure

Having problem call my function in stored procedure.

Below I created a function: makePoint. I want to use this in a stored procedure

select * from my_table where dbo.makePoint(lon, lat).STWithin(my polygon)

But I can not use the function.

The error message:

the function is can not find the user defined function "makePoint".

What am I missing? I have created the SP and function in dbo.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Inline Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE OR ALTER FUNCTION dbo.makePoint(@lon FLOAT, @lat FLOAT)  
RETURNS Geometry   
AS   
BEGIN  
    DECLARE @g Geometry   
    SET @g = Geometry::Point(@lon, @lat, 4326)  

    RETURN @g
END
GO

and my stored procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER PROCEDURE dbo.spGetItemsInRegion @regionId INT
AS
BEGIN
    SET NOCOUNT ON
    declare @geo as Geometry
    select @geo = polygon from something where id = @regionId

    select * from my_table where dbo.makePoint(lon, lat).STWithin(@geo)    
END
GO

Upvotes: 0

Views: 640

Answers (2)

Peter B
Peter B

Reputation: 24147

STWithin returns 0 or 1, and as a result you need to compare the function result to one of those values.

(Note that 1 and 0 do not behave as booleans in T-SQL)

Modified code:

select * from my_table
where dbo.makePoint(lon, lat).STWithin(@geo) = 1

Other than that the code should just work, see dbfiddle.

Upvotes: 2

PNJ
PNJ

Reputation: 1

please check your where condition this should be like below

SELECT * FROM my_table WHERE someting= dbo.makePoint(lon, lat).STWithin(@geo);

Upvotes: 0

Related Questions