Constructing dynamic columns from parameters in Sybase

Question

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

Answer 2

1. SET NOCOUNT {ON|OFF} to turn off row count messages.

2. Yes, you need to you dynamic SQL to change the structure or content of the result set (either the column list or the WHERE clause).