从 Sybase 中的参数构造动态列

发布于 2024-08-25 07:16:46 字数 810 浏览 1 评论 0原文

我正在尝试在 Sybase 中编写存储过程（SP）。

SP 采用 5 个 varchar 参数。

根据传递的参数，我想构造要从特定表中选择的列名。

以下作品：

DECLARE @TEST VARCHAR(50) 选择@TEST =“国家” --print @TEST

执行("选择不同的 id_country AS id_level, Country AS nm_level FROM tempdb..tbl_books 内连接（tbl_ch2_bespoke_report 内连接 tbl_ch2_bespoke_rpt_mapping ON tbl_ch2_bespoke_report.id_report = tbl_ch2_bespoke_rpt_mapping.id_report） ON id_" + @TEST + "= tbl_ch2_bespoke_rpt_mapping.id_pnl_level WHERE tbl_ch2_bespoke_report.id_report = 14")

但给了我多个结果：

1 1 行受影响。1

id_level    nm_level

4376 XYZ
2 4340 ABC

但是我想只获得第二个结果。

我是否需要使用动态 SQL 来实现此目的？

非常感谢您的帮助。

——查帕克斯

原文

I'm trying to write a stored proc (SP) in Sybase.

The SP takes 5 varchar parameters.

Based on the parameters passed, I want to construct the column names to be selected from a particular table.

The below works:

DECLARE @TEST VARCHAR(50)
SELECT @TEST = "country"
--print @TEST

execute("SELECT DISTINCT id_country AS id_level, Country AS nm_level
FROM tempdb..tbl_books INNER JOIN
(tbl_ch2_bespoke_report INNER JOIN tbl_ch2_bespoke_rpt_mapping
ON tbl_ch2_bespoke_report.id_report = tbl_ch2_bespoke_rpt_mapping.id_report)
ON id_" + @TEST + "= tbl_ch2_bespoke_rpt_mapping.id_pnl_level
WHERE tbl_ch2_bespoke_report.id_report = 14")

but gives me multiple results:

1 1 row(s) affected.

id_level    nm_level

1 4376 XYZ
2 4340 ABC

I would like to however only obtain the 2nd result.

Do I need to necessarily use dynamic SQL to achieve this?

Many thanks for your help.

--Chapax

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

橘虞初梦 2024-09-01 07:16:46

如果我理解正确，您希望消除“受影响的 1 行”。线。如果是这样，“set nocount on/off”选项应该可以解决问题：

declare @something int
declare @query varchar(2000)
set nocount  on
select @something=30
select @query = "SELECT * FROM a_table where id_row = " + convert(varchar(10),@something) 
set nocount  off
exec (@query)

或者

declare @something int
declare @query varchar(2000)
set nocount  on
select @something=30
set nocount  off
SELECT * FROM a_table where id_row = @something

If I'm understanding you correctly, you'd like to eliminate the "1 row(s) affected." line. If so, the "set nocount on/off" option should do the trick:

declare @something int
declare @query varchar(2000)
set nocount  on
select @something=30
select @query = "SELECT * FROM a_table where id_row = " + convert(varchar(10),@something) 
set nocount  off
exec (@query)

declare @something int
declare @query varchar(2000)
set nocount  on
select @something=30
set nocount  off
SELECT * FROM a_table where id_row = @something

回复收藏 0 原文