July 9, 2008 at 1:41 am
Hi all,
Making use of parameters, how do I pass parameter to choose the columns/fields of a table.
I know parameters are used in WHERE clauses to filter data but can parameters be used to choose the Column of a table ie. in a SELECT clause
(The sql statement below does not work, as I have found out)
SELECT @Column
FROM For_Sale
*I have attached a jpeg picture to help you understand my problem*
Thanks
July 9, 2008 at 6:18 am
You would probably use a CASE statement to achieve this, for example:
SELECT
CASE
WHEN @Column='Bike' THEN TBL.Bikes
WHEN @Column='Clothes' THEN TBL.Clothes
WHEN @Column='Access' THEN TBL.Accessory
ELSE ''
END AS DropBox2
FROM TableName
This should work for you.
Regards,
Nigel West
UK
July 9, 2008 at 6:29 am
Thanks Nigel.
Can you please tell me, is tbl = tablename?
July 9, 2008 at 6:32 am
Short answer is YES, you don't need it if the select is from one table, I normally use an alias on the select which makes the typing shorter, for example:
SELECT
CASE
WHEN @Column='Bike' THEN TBL.Bikes
WHEN @Column='Clothes' THEN TBL.Clothes
WHEN @Column='Access' THEN TBL.Accessory
ELSE ''
END AS DropBox2
FROM TableName TBL
Note that the TBL after FROM TableName is where the alias is.
Nigel West
UK
July 9, 2008 at 6:35 am
I get an error, "the parameter is incorrect"
SELECT CASE WHEN @col = '1' THEN For_Sale.Bike
WHEN @col = '2' THEN For_Sale.Clothing
WHEN @col = '3' THEN For_Sale.Accessories
END AS DropBox2
FROM For_sales
July 9, 2008 at 6:41 am
SELECT CASE WHEN @col = '1' THEN For_Sale.Bike
WHEN @col = '2' THEN For_Sale.Clothing
WHEN @col = '3' THEN For_Sale.Accessories
END AS DropBox2
FROM For_sales
The @col should be the name of the parameter you have already created (your first parameter).
Other thing is that your table name is different in the case statement to the from statement (For_Sale vs For_sales).
Nigel West
UK
July 9, 2008 at 6:43 am
Thanks so much Nigel, I've been googling whole day and even was told to use stored procedures... Your method works like a star.:D
July 9, 2008 at 6:45 am
My pleasure, pleased to help.
Stored procedures, just as a matter of interest, would need exactly the same coding in the select statement!!
Nigel West
UK
July 10, 2008 at 2:31 am
Another way is to setup all the columns in the report but give the user a multi-value parameter to choose which columns they want. On the basis of their choice you just hide (Hidden = True) the columns which aren't wanted.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply