Dynamically building a SQL statement 1

[Viruland]

I created a SP that shows me the last IDNr for a certain table. The problem is that the variable @nr never gets a value. Can someone tell me what is wrong here

create proc test
@aTable as nvarchar(255)
AS
declare @sql as nvarchar(2000)
declare @nr as nchar(12)

set @sql = 'SELECT ' + convert(nchar(12),@nr) + ' = MAX(IDNr) FROM ' + @aTable

print @nr



Live fast, die young and leave a beautiful corpse behind.

 

[JamesLean]

Look up sp_executesql in BOL:

DECLARE @tbl sysname,
	@num int,
	@sql nvarchar(1000)

SET @tbl = 'table_name'

SET @sql = 'SELECT @n = MAX(IDNr) FROM ' + @tbl

EXEC sp_executesql @sql, N'@n int OUTPUT', @num OUTPUT

PRINT @num

--James