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.