Variable Syntax for User Input

Question

Post reply

Variable Syntax for User Input

AVB

SSChampion

Points: 11418
More actions
April 19, 2005 at 1:30 pm

#163369

I am writing this query so users could input values for two variables from drop downlists in Reporting Services. The two variables will be: @Operator which will be (=,<=,etc) and @EquipmentCount which will be a number.
When I run this it says the command completed. It doesn't say that those variables that aren't supplied need to be supplied.
DECLARE
@Statement NVARCHAR(100),@Operator NVARCHAR, @EquipmentCount INT,@sSQL NVARCHAR(200)
SET @Statement = 'select count (*) as carrier_count from v_MTSTM_EquipmentCountByCarrier where equipment_count'
SET @sSQL = @Statement + @Operator + @EquipmentCount
And if I run this with the user inputed variables set then it says Error converting the nvarchar value 'select count (*).....' to a column of data type int.
DECLARE
@Statement NVARCHAR(100),@Operator NVARCHAR, @EquipmentCount INT,@sSQL NVARCHAR(300)
SET @Statement = 'select count (*) as carrier_count from v_MTSTM_EquipmentCountByCarrier where equipment_count'
SET @Operator = '='
SET @EquipmentCount = '20'
SET @sSQL = @Statement + @Operator + @EquipmentCount
Any ideas????? TIA

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Create proc MyProc @Operator as varchar(2), @EquipmentCount as INT,

AS

Set nocount on

select count (*) as carrier_count from dbo.v_MTSTM_EquipmentCountByCarrier where 0 <

case when @Operator = '<' and equipment_count ' and equipment_count > @EquipmentCount then 1

when @Operator = '=' and equipment_count = @EquipmentCount then 1

...

else 0 end

set nocount off

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

SET @sSQL = @Statement + @Operator + CONVERT(nvarchar,@EquipmentCount)

Andy

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You don't need dynamic sql to do this task, please stay away from it.

AVB SSChampion Points: 11418 More actions · Answer 4

Thanks for the suggestions. Dynamic SQL is new to me. I still have been unsuccessfull. I need to read about the code both of you have supplied so I may understand exactly what is happening. I'll post what I end up with. And feel free to post any other suggestions! Thanks again for your time!!!!!!!!