存储过程中执行存储过程，并把结果赋值给output参数

ALTER PROCEDURE [dbo].[xl_StockWarningQuery]
    @nCargoID bigint,
    @sCargoName varchar,
    @nCargoSup int,
    @nCargoBrand int,
    @sAdminName varchar,
    @pagesize int,
    @pageindex int,
    @count int output
AS
BEGIN
    
    declare @sql nvarchar(4000)
    declare @sql1 nvarchar(4000)

    set @sql = 'select * from
                (select
                ROW_NUMBER() OVER (order by a.sName asc) rownumber,
                a.nID as nCargoID,
                a.sName as sCargoName,
                c.sCompany as sSupName,
                e.sName as sCargoSize,
                f.sName as sCargoColor,
                g.sBrandName as nCargoBrand,
                b.nAmount as nCargoAmount,
                b.nVAmount as nCargoVAmount
                from xl_Cargo a
                left join xl_Inventory b on a.nID = b.nCargoID
                left join xl_Suppliers c on a.nSup = c.nID
                left join xl_Admin d on c.nManager = d.nID
                left join xl_Size e on a.nSizeClass = e.nID
                left join xl_Color f on a.nColorClass = f.nID
                left join xl_Brand g on a.nBrand = g.nID
                where a.nType=1 and b.nVAmount <= 2';
    if (@nCargoID > 0)
    begin
        set @sql = @sql + 'and a.nID = '+str(@nCargoID);
    end
    if (len(@sCargoName)>0)--
    begin
        set @sql = @sql + 'and a.sName = '+@sCargoName;
    end
    if (@nCargoSup>0)
    begin
        set @sql = @sql + 'and a.nSup = '+str(@nCargoSup);
    end
    if (@nCargoBrand>0)
    begin
        set @sql = @sql + 'and a.nBrand = '+str(@nCargoBrand);
    end
    if (len(@sAdminName)>0)
    begin
        set @sql = @sql + 'and d.sAdminName ='+@sAdminName;
    end

    set @sql = @sql +')t where t.rownumber between '+str(@pagesize*(@pageindex-1)+1)+' and '+str(@pagesize*@pageindex);
    --返回总行数---------------------------------------------------------------------------------------------------------
    --declare @MyTable table(nVal int)
    set @sql1 = 'select @count_output = count(*)
                from xl_Cargo a
                left join xl_Inventory b on a.nID = b.nCargoID
                left join xl_Suppliers c on a.nSup = c.nID
                left join xl_Admin d on c.nManager = d.nID
                left join xl_Size e on a.nSizeClass = e.nID
                left join xl_Color f on a.nColorClass = f.nID
                left join xl_Brand g on a.nBrand = g.nID
                where a.nType=1 and b.nVAmount <= 2';
    if (@nCargoID > 0)
    begin
        set @sql1 = @sql1 + 'and a.nID = '+str(@nCargoID);
    end
    if (len(@sCargoName)>0)--
    begin
        set @sql1 = @sql1 + 'and a.sName = '+@sCargoName;
    end
    if (@nCargoSup>0)
    begin
        set @sql1 = @sql1 + 'and a.nSup = '+str(@nCargoSup);
    end
    if (@nCargoBrand>0)
    begin
        set @sql1 = @sql1 + 'and a.nBrand = '+str(@nCargoBrand);
    end
    if (len(@sAdminName)>0)
    begin
        set @sql1 = @sql1 + 'and d.sAdminName ='+@sAdminName;
    end
    ---------------------------------------------------------------------------------------------------------------------
    exec sp_executesql @sql1, N'@count_output INT OUTPUT', @count_output = @count OUTPUT
    exec(@sql)
END