如何从动态查询中选择top（1）max（Data）并将其设置为变量，以便在另一个查询中使用。 [英] How to select top (1) max (Data) from dynamic query and set it to variable, to use in another query.

问题描述

Declare @QryMax nvarchar(max)
Declare @RangeFrom VARCHAR(MAX),
Declare @RangeTo VARCHAR(MAX),
Declare @RangeFilter VARCHAR(MAX),
Declare @OneMB int = 1024*1024

set @QryMax = 'select top (1) MAX( SizePart) Data
            from (
                    select DATEPART('+ @RangeFilter+ ',CreatedOn) as RangeFilter , (FileSize/('+CONVERT (VARCHAR(10), @OneMB)+')) as SizePart from TblAssetDetails
                        where IsDeleted = ''False'' and CreatedOn >= '''+@RangeFrom+''' and CreatedOn <= '''+@RangeTo +'''
                 ) as tbl
            group by RangeFilter'
exec @QryMax

i想要在变量中设置@QryMax的结果（它将是int值）和

i want to set result of @QryMax (it will be int value) in a variable and

select * from Get_DataUnit_WithDataDivider(@MaxData)

此函数将以MB，GB，TB和分频器为单位给出进一步的数据处理。

this function will give in unit in MB, GB, TB and divider, for further data processing.

推荐答案

在TSQL中，您可以尝试使用sp_executesql。



sp_executesql： https://msdn.microsoft.com /en-GB/library/ms188001.aspx [ ^ ]



这样的事情：

In TSQL, you could try using sp_executesql.

sp_executesql: https://msdn.microsoft.com/en-GB/library/ms188001.aspx[^]

Something like this:

declare @QryMax nvarchar(max);
declare @SQLString nvarchar(max);
declare @ParmDefinition nvarchar(1000);

set @SQLString = N'select top (1) @QryMax = MAX( SizePart) Data from ...'
set @ParmDefinition = N'@max_OUT int OUTPUT';

exec sp_executesql @SQLString, @ParmDefinition, @max_OUT = @QryMax Output;

select * from Get_DataUnit_WithDataDivider(@QryMax);--note changed from @MaxData in your question

这应该可以帮助你，按照链接查看如何发送参数。



另外测试看看是否你的动态sql正在返回任何记录，因为你可能也遇到了这个组的问题。

That should help you out, following the link to see how to send in parameters.

Also test to see if your dynamic sql is returning any records, as you may also be having issues with the group by.

这篇关于如何从动态查询中选择top（1）max（Data）并将其设置为变量，以便在另一个查询中使用。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！