where - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

where

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-30 : 11:17:23

Hi,
In my where clause I am using the following sql

AND (s.Field1 = @Field1 OR @Field1 is NULL)

The field Field1 is of type float and in the existing stored procedure, the variable @Field1 is of type float too.

The problem is that if in the UI I enter a value of 1.33 then I would like to see all the records that start with 1.33 something like 1.33%
How can I change the existing sql to cater for this please?
Is it something like:
AND (s.Field1 like convert(varchar(50), @Field1) + '%' + OR @Field1 is NULL)

Thank you

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-30 : 11:21:35

Floor(s.Field1 * 100) / 100 = @Field1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-30 : 11:24:35

quote:
Originally posted by russell

Floor(s.Field1 * 100) / 100 = @Field1

This seems to work if I enter say @field1 = 1.3 but it does not return if I enter say 1.30000095432. Note that there is iindeed a record which has the value 1.30000095432
Any thoughts please?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-07-30 : 11:41:06

solved.
It needs to be converted to varchar for every occurance of field.

Thank you

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-08-02 : 07:27:24

DECLARE @Flag TABLE
(
[ID] int identity,
Field float
)

declare @field VARCHAR(10)
set @field = '10.25'

INSERT INTO @Flag (Field)
SELECT 10.25
UNION ALL SELECT 10.257
UNION ALL SELECT 11.00
UNION ALL SELECT 10.21
UNION ALL SELECT 10.255

select * from @Flag

select * from @Flag
where 1 = case WHEN CHARINDEX(@field,CAST(Field AS VARCHAR(10))) > 0 THEN 1 ELSE 0 END

Iam a slow walker but i never walk back

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-03 : 05:45:03

AND (s.Field1 like @Field1+
'%' OR @Field1 is NULL)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources