CODEWITHSUNDEEP
sqlsql-serverdatabasesql-function
Adan Kera
Adan Kera

Reputation: 39

Function SELECT that returns a row in SQL SERVER 2012

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

Upvotes: 1

Views: 12917

Answers (4)

Balanjaneyulu K
Balanjaneyulu K

Reputation: 4314

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

Upvotes: 3

user3656388
user3656388

Reputation: 36

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])

Upvotes: 1

Conrad Lotz
Conrad Lotz

Reputation: 8818

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

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

Upvotes: 2

Related Questions