Constructing dynamic columns from parameters in Sybase

Question

Hi, 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

Answer 1

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)

or

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