October 13, 2004 at 7:28 am
I am trying to generate line numbers when doing a select statement. I have created a udf which I call in the select statement but the line numbers are always the same. Is there a way to save the return value from the udf and then pass this saved value back again so that the number are incremented.
Any help would be greatly appreciated.
Here is my udf and select statement.
**********
create function linecount(@numx as int)
returns int
as
begin
begin
set @numx = @numx+1
end
return @numx
end
***********
declare @numx int
set @numx = 0
select dbo.linecount(@numx) as NO, LoginName, DateChanged, ChangedBy
from audit..passwordchangeaudit
************
October 13, 2004 at 8:48 am
No.
INSERT into a table with an identity column, or use the identity() function with the INTO clause to create a table with an identity column.
Better still, let the client application assign line numbers.
October 13, 2004 at 11:49 am
Hi,
Here is a proc I use to list and enumerate entities from a specific report:
SELECT
identity(INT,1,1) AS [count],
pk_entity,
fk_entityType,
FullName,
DateOfBirth,
Address,
CityStateZip,
gender,
Race,
HairColor,
Eyecolor,
Height,
Weight,
DriversLicenseNumber,
DriversLicenseState,
fullstring,
badgeNumber,
ContactType,
PhotoFile,
fk_Report,
DateDiff(year,DateOfBirth,getdate()) AS age
into #temp
FROM ReportEntityInfoList
WHERE fk_Report=@pk_Report
ORDER BY ContactType
SELECT * FROM #temp
END
It just throws it into #temp while I count them then I read from #temp
hope that helps
Tal McMahon
October 13, 2004 at 11:46 pm
Thanks for the info, it was a great help.
Philip
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply