how to display top 5 records in the table using function.

Hardik Bhavsar

13y1.8kSQL

how to display top 5 records in the table using function.?

i used this query but this query is not wrok on funtcion.
select top 5 * from tablename

using this function only 1 record are display.

alter function fn_TopStudentRecord

(@name varchar(20))

returns varchar(20)

begin

return (select name from Student1 where name=@name)

end

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Iftikar Hussain13y ago
here it is

ALTER FUNCTION fn_TopStudentRecord(@name varchar(20))
RETURNS @student1 TABLE (Id UNIQUEIDENTIFIER NOT NULL, Name varchar(100)
)
AS
BEGIN
INSERT @student1 (Name)
select name from Student1 where name=@name
RETURN END

Regards,
Iftikar
0
Hardik Bhavsar13y ago
The Version is->
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)
0
Iftikar Hussain13y ago
if your version does not support TABLE variable, then you will not able to return multiple output from a function
0
Hardik Bhavsar13y ago
your solution is not work. this also create error ALTER FUNCTION fn_TopStudentRecord(@name varchar(20) RETURNS @student1 student1 (Id UNIQUEIDENTIFIER NOT NULL, Name varchar(100)) AS BEGIN INSERT @student1 (Name) select name from Student1 where name=@name RETURN END ERROR IS----> Msg 102, Level 15, State 1, Procedure fn_TopStudentRecord, Line 2 Incorrect syntax near 'RETURNS'. Msg 1087, Level 15, State 2, Procedure fn_TopStudentRecord, Line 6 Must declare the table variable "@student1". Msg 137, Level 15, State 2, Procedure fn_TopStudentRecord, Line 7 Must declare the scalar variable "@name".
0
Hardik Bhavsar13y ago
thera are 10 records in student1 table and only 1 records are display all name are unique in this table and how to display top 5 records using function in sql
0
Iftikar Hussain13y ago
try like this

ALTER FUNCTION fn_TopStudentRecord(@name varchar(20)
RETURNS @retTable TABLE (Id UNIQUEIDENTIFIER NOT NULL, Name varchar(100))
AS
BEGIN

INSERT @retTable (Name)
select name from Student1 where name=@name

RETURN
END

Regards,
Iftikar
0
Iftikar Hussain13y ago
which version of sql you are using?
how many records are there for the below query

select name from Student1 where name=@name
0
Hardik Bhavsar13y ago
alter function fn_TopStudentRecord (@name varchar(20)) returns varchar(20) begin return (select name from Student1 where name=@name) end
0
Sivaraman Dhamodaran13y ago
Top 5 record in the order that they are inserted?. Read about RowIds....
0
Iftikar Hussain13y ago
Hi,
Can you share your function?

Regards,
Iftikar
0