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