Function SELECT that returns a row in SQL SERVER 2012

Question

I want to create a function that gets the last record from my table, but it didn't work.

My code is :

CREATE FUNCTION GetLastReglement (@CT_Num VARCHAR(17))
RETURNS VARCHAR(17)
AS
BEGIN
    SELECT TOP (1) * 
    FROM F_REGLEMENTT
    WHERE CT_Num=@CT_Num
    ORDER BY CT_Num DESC
RETURN @CT_Num
END

Thanks for your help

PS: I'm novice in SQL

You are limiting `CT_NUM` to a single value and then ordering by it. Hence, it is a constant and any row with a matching value might be returned. SQL tables represent *unordered* sets. There is no "last" row, unless a column specifies the ordering. — Gordon Linoff, May 30 '17 at 11:31
Your function doesn't make sense. Why do you even want a function to begin with? Please edit your question to include sample data and desired results. — Zohar Peled, May 30 '17 at 11:32
If you want to return a row then why don't you use inline table valued function. — M. Adeel Khalid, May 30 '17 at 11:33
@MAdeelKhalid i didn't get it, could you please give me an exemple — Adan Kera, May 30 '17 at 11:34
Possible duplicate of [How to select the last record of a table in SQL?](https://stackoverflow.com/questions/5191503/how-to-select-the-last-record-of-a-table-in-sql) — rghome, May 30 '17 at 11:38

score 2 · Answer 1 · edited Jun 02 '20 at 11:49

2

You can try below one to get last row based on some unique column value.

CREATE FUNCTION GetRowData
(
)
RETURNS TABLE
AS
RETURN (
    SELECT TOP 1 * FROM F_REGLEMENTT ORDER BY CT_Num DESC 
)
END

edited Jun 02 '20 at 11:49

Kitesaint1309

59
2
8

answered May 30 '17 at 11:37

Balanjaneyulu K

3,660
5
24
45

score 1 · Accepted Answer · answered May 30 '17 at 11:40

1

You might require to use return table as below

CREATE FUNCTION GetLastReglement (@CT_Num varchar(17))
returns @rtTable table
(
    --columns in your table F_REGLEMENTT with datatype as below
    col1 nvarchar(50)
    ...
)
as
begin
    insert into @rtTable
    select top (1) * 
    from F_REGLEMENTT
    where CT_Num=@CT_Num
    order by CT_Num desc
return;
end

answered May 30 '17 at 11:40

Kannan Kandasamy

13,405
3
25
38

and if i want to return just the number of the bill, why this code don't work ? `CREATE FUNCTION dbo.GetLastFatt (@DO_Piece VARCHAR(9) ) RETURNS varchar9 as RETURN ( SELECT top (1) DO_Piece FROM F_DOCENTETE WHERE DO_Type =6 ORDER BY DO_Piece DESC )` – Adan Kera May 30 '17 at 12:07

Conrad Lotz · Answer 3 · 2017-05-30T11:41:25.240

0

Try returning the entire row instead of the CT_Num as follow:

CREATE FUNCTION dbo.GetLastReglement (@CT_Num VARCHAR(17)
)
RETURNS TABLE as RETURN (
  SELECT top (1) * 
  FROM F_REGLEMENTT
  WHERE CT_Num=@CT_Num
  ORDER BY CT_Num DESC
)
END

edited May 30 '17 at 11:41

answered May 30 '17 at 11:38

Conrad Lotz

8,200
3
23
27

1

I get this problem Msg 156, Niveau 15, État 1, Procédure GetLastReglement, Ligne 24 Syntaxe incorrecte vers le mot clé 'AS'. Msg 178, Niveau 15, État 1, Procédure GetLastReglement, Ligne 25 Une instruction RETURN avec une valeur de retour ne peut être utilisée dans ce contexte. – Adan Kera May 30 '17 at 11:40

score 0 · Answer 4 · answered May 30 '17 at 11:54

0

You could use the MAX function instead of top(1)*

CREATE FUNCTION [dbo].[GetLastReglement] ()
RETURNS TABLE
AS
RETURN
SELECT * FROM [F_REGLEMENT] WHERE CT_NUM =
(SELECT MAX(CT_NUM) FROM [F_REGLEMENT])

answered May 30 '17 at 11:54

user3656388

26
2

Function SELECT that returns a row in SQL SERVER 2012

4 Answers4