CODEWITHSUNDEEP
sqlsql-server
itman1234
itman1234

Reputation: 57

MS Sql Server - Function not compiling because of selecting from table

In SQL Server, I made a function that will return the sum of the column from a table which is the result of a query to another table. It's easier to understand when looking at the code:

CREATE FUNCTION [dbo].[getPatientMorphineEquivalentDose]
(
    @patientID int
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN (SELECT SUM(j.MilligramMorphineEquivalent) FROM 
    (
        SELECT i.Mg * i.[Morphine Equivalent (mg)] AS MilligramMorphineEquivalent
        FROM
        (
            SELECT PatientMedication.Mg, Medication.[Morphine Equivalent (mg)]
            FROM PatientMedication
            INNER JOIN Medication 
            ON PatientMedication.MedicationID = Medication.Id
            WHERE PatientMedication.PatientID = @patientID
        ) AS i
    ) AS j )
END

I have very little experience with Sql Server so I am not sure if I am doing anything wrong, but from what I researched online this should work. I tried it with a stored procedure as well and it still would not compile.

Upvotes: 0

Views: 176

Answers (1)

sstan
sstan

Reputation: 36523

You didn't specify which error you got, but since you're defining your function with schemabinding, I expect you got an error like:

Cannot schema bind function 'dbo.getPatientMorphineEquivalentDose' because name 'PatientMedication' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

When you use schemabinding, you are expected to prefix the object names with the owner. Notice what the documentation says (emphasis mine):

A function can be schema bound only if the following conditions are true:

  • The function is a Transact-SQL function.
  • The user-defined functions and views referenced by the function are also schema-bound.
  • The objects referenced by the function are referenced using a two-part name.
  • The function and the objects it references belong to the same database.
  • The user who executed the CREATE FUNCTION statement has REFERENCES permission on the database objects that the function references.

So assuming your tables are owned by dbo, make sure to prefix the 2 referenced tables in the query (dbo.PatientMedication and dbo.Medication):

CREATE FUNCTION [dbo].[getPatientMorphineEquivalentDose]
(
    @patientID int
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN (SELECT SUM(j.MilligramMorphineEquivalent) FROM 
    (
        SELECT i.Mg * i.[Morphine Equivalent (mg)] AS MilligramMorphineEquivalent
        FROM
        (
            SELECT PatientMedication.Mg, Medication.[Morphine Equivalent (mg)]
            FROM dbo.PatientMedication
            INNER JOIN dbo.Medication 
            ON PatientMedication.MedicationID = Medication.Id
            WHERE PatientMedication.PatientID = @patientID
        ) AS i
    ) AS j )
END

By the way, unrelated to your error, but the query can be simplified:

CREATE FUNCTION [dbo].[getPatientMorphineEquivalentDose]
(
    @patientID int
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN (SELECT sum(pm.Mg * m.[Morphine Equivalent (mg)])
            FROM dbo.PatientMedication pm
            INNER JOIN dbo.Medication m
            ON pm.MedicationID = m.Id
            WHERE pm.PatientID = @patientID)
END

Upvotes: 2

Related Questions