SQLTeam.com | Weblogs | Forums

MSSMS SQL I am trying to create function which returns total number of people from 2 tablets

Hi I am creating database and I am trying to create function which returns total number of people from 2 tablets.

Table 1
*
CREATE TABLE Zamestnanci
(
id_Zamestnanca int not null,
Meno varchar(50) null,
Priezvisko varchar(50)null,
Adresa varchar(50) null,
Datum_narodenia date null,

PRIMARY KEY (id_Zamestnanca)

);*

Table 2 :
*
CREATE TABLE ObčaniaSR
(
id_Občana int not null,
Meno varchar(50) null,
Priezvisko varchar(50) null,
Adresa varchar(50) null,
Datum_narodenia date null,
Zápis_v_trestnom_registry varchar(50) null,

PRIMARY KEY (id_Občana)

);*

Function I am trying to declare :
*use A20762_AK1DS_Projekt

CREATE OR REPLACE FUNCTION VseciZamestnanci
RETURN id_Zamestnanca IS
total number(2) := 0;
BEGIN
SELECT count(*) into id_Zamestnanca
FROM Zamestnanci;

RETURN id_Zamestnanca;
END;
/ *

I got this Error : *Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'REPLACE'.
Msg 178, Level 15, State 1, Line 10
A RETURN statement with a return value cannot be used in this context.
*
Thank you for your answers and advices.

is this microsoft sql server or some other db technology?

microsoft sql server

what version? and remember the syntax or code differs from one version of SQL to another. and also from db product such as POSTGRE vs SQL SERVER

It's SSMS 18

2018

1 Like

CREATE OR REPLACE FUNCTION it not a sql server syntax

Microsoft SQL Server 2019 (RTM-GDR) (KB4583458) - 15.0.2080.9 (X64)

use this format not CREATE OR REPLACE
Follow this documentation

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL  
    DROP FUNCTION ufnGetInventoryStock;  
GO  
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)  
RETURNS int   
AS   
-- Returns the stock level for the product.  
BEGIN  
    DECLARE @ret int;  
    SELECT @ret = SUM(p.Quantity)   
    FROM Production.ProductInventory p   
    WHERE p.ProductID = @ProductID   
        AND p.LocationID = '6';  
     IF (@ret IS NULL)   
        SET @ret = 0;  
    RETURN @ret;  
END;

IF OBJECT_ID (N'dbo.A20762_AKIDS_Projekt', N'FN') IS NOT NULL
DROP FUNCTION Mnozstvo;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@id_Zamestnanca int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.Mnozstvo)
FROM Zamestnanci p
WHERE i.id_Zamestnanca = @id_Zamestnanca
AND p.LocationID = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;

I don't know what am I doing wrong I got : *Msg 4104, Level 16, State 1, Procedure ufnGetInventoryStock, Line 9 [Batch Start Line 3]
The multi-part identifier "i.id_Zamestnanca" could not be bound.
Msg 207, Level 16, State 1, Procedure ufnGetInventoryStock, Line 10 [Batch Start Line 3]
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Procedure ufnGetInventoryStock, Line 7 [Batch Start Line 3]
Invalid column name 'Mnozstvo'.
*

i.id_Zamestnanca where is this i coming from, your alias is not i

FROM Zamestnanci p

your alias is p

i replace it with i and I got *Msg 4104, Level 16, State 1, Procedure ufnGetInventoryStock, Line 10 [Batch Start Line 5]
The multi-part identifier "p.LocationID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ufnGetInventoryStock, Line 7 [Batch Start Line 5]
The multi-part identifier "p.Mnozstvo" could not be bound.
*

who knows what you have now changed. you are doing things without understanding them. not best way to learn

first understand what alias means in SQL SErver

IF OBJECT_ID (N'dbo.', N'FN') IS NOT NULL
DROP FUNCTION Mnozstvo;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@id_Zamestnanca int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(i.id_Zamestnanca)
FROM Zamestnanci i
WHERE i.id_Zamestnanca = @id_Zamestnanca
AND i.id_Zamestnanca = '6';
IF (@ret IS NULL)
SET @ret = 0;
RETURN @ret;
END;* I just made it run but it don't return any value it only returns Commands completed successfully. *

You just created the function now you have to use it

How can I call it? It is similiar calling a function like in languages C or C# or Python bcs that's languages I am primary working with.

Yes it is, from SQL 2016 on, at least.

1 Like

It's more efficient to avoid local variables in a function unless you absolutely must use them. Thus, do this instead:

IF OBJECT_ID (N'dbo.', N'FN') IS NOT NULL
DROP FUNCTION Mnozstvo;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@id_Zamestnanca int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
RETURN (
    SELECT ISNULL(SUM(i.id_Zamestnanca), 0)
    FROM Zamestnanci i
    WHERE i.id_Zamestnanca = @id_Zamestnanca
    AND i.id_Zamestnanca = '6'
)
END;

I actually have it written in SQL. I just asked if it is simillar because I mostly work in C or C# or Python.