SQL dynamic auto incrementing column in stored procedure

I'm trying to 开发者_C百科write a stored procedure [SQL 2008] with a dynamic, auto incrementing column and the syntax for this has escaped me.

If I have a table called widget with a single column called widget_nm my goal is to

SELECT [???] AS widget_id, widget_nm FROM widget

and get the following result

widget_id | widget_nm
1         | bob
2         | sally
3         | fred

I remembered how to do this once, but my memory has failed me ... any advice?

Happy Holidays!

You've not mentioned what dialect of sql you're using,

If you're on mssql(Tsql) 2005+ use the Row_Number() function

You can use ROW_NUMBER in SQL Server 2005 and later:

SELECT ROW_NUMBER() OVER (ORDER BY widget_nm) AS widget_id, widget_nm
FROM widget
ORDER BY widget_nm

which will number in order of widget_nm

If you're using SQL Server 2005, this is trivial.