Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maximus_vj
Yak Posting Veteran
88 Posts |
 Posted - 2004-11-02 : 16:55:56
|
| Hello,I have a table TABLE1 with the following fields:(Num_ID int,Name varchar(50),column1 text,column2 text,column3 Text)The user can generate a report on this table. While doing this he can select one or more text fields and also he can specify the order so that the report will have only the selected Text Fields and in that order.Create Procedure s_test (@fld1On tinyint = 1, -- if 1 display @fld2On tinyint = 1,@fld3On tinyint = 1, -- column names these can be in any order@col1Name varchar(50)= 'column1',@col2Name varchar(50)= 'column2',@col3Name varchar(50)= 'column3',)If I have SP like above how can I write a SP without Dynamic SQL so that I can select the columns based on these parameters. If @fld1 is 1 then I want to select whatever name that is specified in teh @col1Name parameter.Thanksmaximus |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-11-02 : 18:18:32
|
| One option would be to return all three and only display (In the presentation tier) the columns which are of interest to the user. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-02 : 21:56:00
|
quote:
Originally posted by ehorn
One option would be to return all three and only display (In the presentation tier) the columns which are of interest to the user.
i agree, it'll be more simple than thinking ways on how to do it dynamically and creating extra sprocs/fxn just to do that. I usually "hide" the fields not needed for reports and "show" them when the user needs them. IMHO, it'll be more resource intensive if you keep requesting sql to process the query everytime the user selects/deselects fields (worst case is if it's in one session only)--------------------keeping it simple... |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-11-03 : 10:43:31
|
| Hello,Thank you for your ideas. maximus. |
|
|
|
|
|
|
|
|
Dynamically Selecting Cloumns in a Report