动态语句返回值保存进变量,解决结贴!

[kuangdp]

返回我所要查询表中中间位置的字段值,需要保存下来,然后我所查询的表的条数是根据我的条件 Y=@LASTYEAR1 来变的,帮看下我写的有什么问题吗?

DECLARE @LASTYEAR1 VARCHAR(4),

@XORDER INT ,

@VALUE1 NUMERIC(18,4) ,

@S1 VARCHAR(2000),


SELECT TOP 1 @LASTYEAR1=Y FROM TB ORDER BY Y DESC

SELECT @XORDER=CASE WHEN COUNT(1)%2=0 THEN COUNT(1)/2 ELSE COUNT(1)/2+1 END
FROM TB WHERE Y=@LASTYEAR1

SET @S1='SELECT TOP 1 @VALUE1=VALUE1 FROM (SELECT TOP '+CAST(@XORDER AS VARCHAR(10))+' VALUE1 FROM TB WHERE Y='''+@LASTYEAR1+''' ORDER BY VALUE1) A ORDER BY VALUE1 DESC'

EXEC SP_EXECUTESQL @S1,N'@VALUE1 NUMERIC(18,4)',@VALUE1 OUTPUT

SELECT @VALUE1

[昵称被占用了]

DECLARE   @LASTYEAR1   VARCHAR(4),   
  
@XORDER INT   , 

@VALUE1   NUMERIC(18,4)     , 

@S1 NVARCHAR(2000),   --这里改类型
  
  
SELECT   TOP   1   @LASTYEAR1=Y   FROM   TB   ORDER   BY   Y   DESC 

SELECT   @XORDER=CASE   WHEN   COUNT(1)%2=0   THEN   COUNT(1)/2   ELSE   COUNT(1)/2+1   END 
FROM   TB   WHERE   Y=@LASTYEAR1 

SET   @S1='SELECT   TOP   1   @VALUE1=VALUE1   FROM   (SELECT   TOP   '+CAST(@XORDER   AS   VARCHAR(10))+'   VALUE1   FROM   TB     WHERE               Y='''+@LASTYEAR1+'''   ORDER   BY   VALUE1)   A   ORDER   BY   VALUE1   DESC' 

  EXEC   SP_EXECUTESQL   @S1,N'@VALUE1   NUMERIC(18,4) OUTPUT',@VALUE1   OUTPUT   --这里加 OUTPUT

SELECT   @VALUE1

[昵称被占用了]

改了两个地方，有注释的行

[wzy_love_sly]

因该没别的改了

[kuangdp]

简洁,精辟,我喜欢~结帖!

[昵称被占用了]

改类型那行多了个逗号