SQLTeam.com | Weblogs | Forums

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

#1

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.

#2

is this microsoft sql server or some other db technology?

#3

microsoft sql server

#4

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

#5

It's SSMS 18

#6

2018

1 Like
#7

CREATE OR REPLACE FUNCTION it not a sql server syntax

#8

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

#9

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;
#10

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'.
*

#11

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

FROM Zamestnanci p

your alias is p

#12

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.
*

#13

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

#14

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. *

#15

You just created the function now you have to use it

#16

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.

#17

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

1 Like
#18

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;
#19
#20

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