As the others have said, it really isn't clear what you are trying to do or why, however, based on your statement
Quote:
and if No is 2 then
Select StudentID, StudentName, Std, Percentage
From TBLStudent
Union All
Select StudentID, StudentName, Std, Percentage
From TBLStudent
then you are expecting to get the
entire contents of TBLStudent repeated
No
number of times. To achieve that you can use the query from Solution 1 in a cross-join e.g.
Select StudentID, StudentName, Std, [Percentage]
From TBLStudent
cross join
(SELECT TOP (@No) '' As A FROM sys.all_columns) as A;
BUT I suspect what you really want is to get the first
No
of records from that table in which case your query should simply be
select top (@No) StudentID, StudentName, Std, [Percentage]
from TBLStudent;
Instead of showing us what the query would look like, give some sample data and your expected results - it is an incredibly powerful way of showing us the problem