I am writing a procedure to produce an int output variable, but I'm not sure how to do this using dynamic sql. If I execute the below procedure I get the @AnlyNum value displayed in the results screen, but I just want @AnlyNum variable set with a value so I can use it. Thank you.
Create procedure [dbo].[sp_test] @Db varchar(50), @RwNum int, @AnlyNum int output
As
Begin
Declare @Sql nvarchar(max) =
'Select ''@AnlyNum'' = (Select AnlyId From '+@Db+'..Test order by AnlyId desc OFFSET '+convert(varchar(10),@RwNum)+' rows fetch next 1 rows only)'
End
exec(@Sql)