variable drop down lists

Question

variable drop down lists

AVB

SSChampion

Points: 11418
More actions
April 18, 2005 at 2:27 pm

#164324

I want to create two drop down lists that supply the variables for a SP in the report.
The first drop down should be a list of:
Equal,Less Than Equal To,Greater Than Equal To, etc..
The second drop down should be a number value.
I am not sure of the syntax for the SP. THis is what I have so far:
SELECT     COUNT(*) AS Carrier_count
FROM      EquipmentCountByCarrier
WHERE     Equipment_Count @Variable1, @EquipmentCount
Thanks for any posts!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

I thought you'd have to use dynamic SQL to do this (ie create a string and then 'EXEC' it). An alternative (but possibly not workable depending on the size of data returned (in rows) could be to use the filters in the dataset.

The dynamic sql approach would be having your SP accept the two parameters and then concatenate these to a predfined SQL string and then executing it. using your code it could look like

SET @sSQL = 'SELECT COUNT(*) AS Carrier_count FROM EquipmentCountByCarrier WHERE Equipment_Count '

SET @sSQL = @sSQL + @Variable1 + ' ' + @EquipmentCount

EXEC @sSQL

Steve.

Jon Spink SSCarpal Tunnel Points: 4010 More actions · Answer 2

I implemented this (in Access) by just creating a function which takes the operator, number parameter and data value as parameters and returns a true or false which can be tested in a WHERE clause.

AVB SSChampion Points: 11418 More actions · Answer 3

Thanks for the replies! I have a few more questions.

I've been working on it and I i have a problem converting my @test-2 variable to a value to a column of data type int. How do you convert the output of that?

Also the @Operator variable is going to be operators (=,<,etc) what data type is that?

DECLARE

@test-2 VARCHAR(70),@Operator INT, @EquipmentCount INT,@sSQL NVARCHAR(70)

SET @test-2 = 'select count (*) as carrier_count from v_MTSTM_equipmentcountbycarrier where equipment_count'

SET @sSQL = @test-2 + @Operator + @EquipmentCount

EXEC @sSQL

Thank you for any suggestions!

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 4

>> Also the @Operator variable is going to be operators (=,<,etc) what data type is that?

It would be varchar or even char though I'd recommend varchar.

>> I've been working on it and I i have a problem converting my @test-2 variable to a value to a column of data type int. How do you convert the output of that?

You mean get the number of records affected value into a variable ? You can use the @@ROWCOUNT for that right after the EXEC(@sSql).

******************
Dinakar Nethi
Life is short. Enjoy it.
******************