导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

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

kuangdp 2008-01-23 11:53:09
返回我所要查询表中中间位置的字段值,需要保存下来,然后我所查询的表的条数是根据我的条件 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
...全文
27 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
改类型那行多了个逗号
回复
kuangdp 2008-01-23
简洁,精辟,我喜欢~结帖!
回复
wzy_love_sly 2008-01-23
因该没别的改了
回复
改了两个地方,有注释的行
回复
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告