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

Question

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

Iftikar Hussain · Answer

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

Hardik Bhavsar · Answer

The Version is-> Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)

Iftikar Hussain · Answer

if your version does not support TABLE variable, then you will not able to return multiple output from a function

Hardik Bhavsar · Answer

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

Hardik Bhavsar · Answer

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

Iftikar Hussain · Answer

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

Iftikar Hussain · Answer

which version of sql you are using? how many records are there for the below query select name from Student1 where name=@name

Hardik Bhavsar · Answer

alter function fn_TopStudentRecord (@name varchar(20)) returns varchar(20) begin return (select name from Student1 where name=@name) end

Sivaraman Dhamodaran · Answer

Top 5 record in the order that they are inserted?. Read about RowIds....

Iftikar Hussain · Answer

Hi, Can you share your function? Regards, Iftikar