T-SQL Unresolved reference in scalar function

0
votes

I'm trying to create a scalar function that calculates how many financial years have passed for an organization based on a date and organization ID passed to the function. I need to look up the organization based on its ID, get when its financial year begins, then do the calculation and return.

Both references to the tblOrganizations table are showing SQL71005 and are not resolving, even though both SSMS and VS are showing auto-complete for the table.

I'm far from an SQL guru. Any suggestions why they're not resolving?

CREATE FUNCTION [dbo].[fnGetFinanicalYear]
    (
        @dateOfInterest datetime,
        @organizationId bigint
    )
    RETURNS INT
    AS
    BEGIN

        DECLARE @fyBegins datetime, @ret as bigint
        SELECT TOP 1 @fyBegins = [dbo].[tblOrganizations].[financialYearBegins] WHERE [dbo].[tblOrganizations].[id] = @organizationId;

        SELECT @ret = (CONVERT(int,CONVERT(char(8),@dateOfInterest,112))-CONVERT(char(8),@fyBegins,112))/10000;

        RETURN @ret;  
    END
2
sql-servertsql
It doesn't appear that you should really need top 1. And supposing that you really need it, it's not a good idea to use it without order by. - shawnt00
Maybe this case won't even happen. Did you really want 20170228 - 20160229 to be 0 years? And while I'm being a critic, it would be impossible for @ret to ever return a number that won't fit in a regular int. - shawnt00

2 Answers

2
votes

You require a from clause if interacting with a table:

SELECT TOP 1 
  @fyBegins = financialYearBegins 
FROM
  tblOrganizations
WHERE 
  id = @organizationId;
0
votes

I've not seen this syntax before in T-SQL:

SELECT TOP 1 @fyBegins = [dbo].[tblOrganizations].[financialYearBegins] WHERE [dbo].[tblOrganizations].[id] = @organizationId;

Try this:

SELECT TOP 1 @fyBegins = financialYearBegins FROM dbo.tblOrganizations WHERE id = @organizationId;

If this doesn't fix it, you may want to check permissions to ensure that the 'user' executing the function has SELECT rights on the table.