查询结果付值给变量的问题。

[hj3793]

use Northwind

Declare @Name varchar(50),
@ID varchar(50),
@Sql varchar(100),
@Res int

Set @Name='Orders'
Set @ID='OrderID'


--Set @Sql= 'Select '+@Res+'=Max('+@ID+')+1 from'+ @Name
--Set @Sql= 'Select '+@Res+'=Max(@ID)+1 from'+ @Name
--Set @Sql= 'Select @Res=Max(@ID)+1 from '+ @Name
Set @Sql= 'Select Max('+@ID+')+1 from '+ @Name
Print @Sql

Execute(@Sql)
---------------------------------------
怎样才能把上面的@Sql语句执行结果付值到 @Res 变量中？？？
偶试过了几种方法都是错误的，敬请高手解答。

[paoluo]

use Northwind

Declare @Name varchar(50),
@ID varchar(50),
@Sql Nvarchar(100),
@Res int

Set @Name='Orders'
Set @ID='OrderID'

Set @Sql= 'Select @Res = Max('+@ID+')+1 from '+ @Name
EXEC sp_executesql @Sql, N'@Res int Output', @Res Output
Select @Res

[paoluo]

use Northwind

Declare @Name varchar(50),
@ID varchar(50),
@Sql Nvarchar(100), --類型改為Nvarchar
@Res int

Set @Name='Orders'
Set @ID='OrderID'

Set @Sql= 'Select @Res = Max('+@ID+')+1 from '+ @Name
EXEC sp_executesql @Sql, N'@Res int Output', @Res Output --使用sp_executesql
Select @Res
--Result
/*
11078
*/

[hrb2008]

Declare @Name varchar(50),
@ID varchar(50),
@Sql varchar(100),
@Res int,
@maxid int
Set @Name='Orders'
Set @ID='OrderID'
Set @Sql= 'Select @mid=Max('+@ID+')+1 from '+ @Name
set @ParmDefinition =N'@mid int OUTPUT'
EXECUTE sp_executesql @SQL,@ParmDefinition ,@mid =@maxid OUTPUT
select @maxid