Cannot get output variable from stored procedure when procedure written in dynamic sql-CodePudding

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)

CodePudding user response：

This removes SQL injection concerns by properly escaping the database name and also dynamically executing against that database instead of embedding the database name in the command. Also, you don't need @RwNum to be dynamic.

CREATE PROCEDURE dbo.test
  @Db      sysname,
  @RwNum   int,
  @AnlyNum int output
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @exec nvarchar(max) = QUOTENAME(@Db)   N'.sys.sp_executesql',
          @sql nvarchar(max) = N'SELECT @AnlyNum = AnlyId 
            From dbo.Test order by AnlyId desc 
            OFFSET @RwNum rows fetch next 1 rows only);';

  EXEC @exec @sql, N'@AnlyNum int output, @RwNum int',
    @AnlyNum output, @RwNum;
END