custom select statement. - 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 2000 Forums

SQL Server Development (2000)

custom select statement.

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-03 : 17:25:54

I have a column of type 'bit' in the database which is basically stores 1 as 'Yes' and 0 as 'No'.
Now, in the standard select statement.. I have to say

Select * from table where column = 1

But, I want to provide the interface such that any user don't have to know about this bit codes and they can query like

Select * from table where column = 'Yes'.

Is there a way to do this??

Thanks,
ujjaval

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-03 : 17:29:01

You do this kind of work in your application and not in the database.

Tara Kizer
aka tduggan

SamC
White Water Yakist

3467 Posts

Posted - 2006-01-03 : 19:55:08

quote:
Originally posted by tkizer

You do this kind of work in your application and not in the database.

Tara's right, but if you must...

WHERE column = CASE WHEN @UserValue = 'Yes' THEN 1 ELSE 0 END

Kristen
Test

22859 Posts

Posted - 2006-01-04 : 07:22:13

ujjaval: If you are going to do it in SQL what about if the @UserValue is neither "yes" nor "no"? You'll wind up with a bunch of validation logic etc. ...

Kristen

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-04 : 17:37:54

Kristen: yes, you are right. and I wouldn't go for that. I reckon if I can simply store 'yes' or 'no' in the column of varchar. That will do just fine. What do you think?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 20:15:55

quote:
Originally posted by ujjaval

Kristen: yes, you are right. and I wouldn't go for that. I reckon if I can simply store 'yes' or 'no' in the column of varchar. That will do just fine. What do you think?

That's a really bad idea. You will use a lot more space in the table to store the VARCHAR column than to store a BIT and it will take longer to process the queries.

Just use SamC's solution:
WHERE column = CASE WHEN @UserValue = 'Yes' THEN 1 ELSE 0 END

If you really need to provide this to an end-user and they are too dumb to understand 1 = YES and 0 = NO, put the logic it in a scalar function:


create function UDF_YES_OR_NO
	( @YES_OR_NO varchar(3) )
returns bit
as 
begin
if @YES_OR_NO = 'yes' return 1
if @YES_OR_NO = 'no'  return 0
return null
end
go

-- Example of use
select 
	YES	= dbo.UDF_YES_OR_NO('yes'), 
	NO	= dbo.UDF_YES_OR_NO('no'),
	OTHER	= dbo.UDF_YES_OR_NO('')

GO
DROP function UDF_YES_OR_NO


Results:
YES  NO   OTHER 
---- ---- ----- 
1    0    NULL

(1 row(s) affected)

-- Used In select statement
WHERE column = dbo.UDF_YES_OR_NO('yes')

CODO ERGO SUM

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-01-04 : 20:47:39

Michael I had thought of it. But as far as I know, SQL Server 7.0 does not support user defined functions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-05 : 00:40:25

The simple approach is to use Validation at Client side and send 1 or 0
What is your Front End application?
In VB, you can write

If userInput='Yes' then
Select * from table where column = 1
else
Select * from table where column = 0

or

Select * from table where column = IIF(userinput='Yes',1,0)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-05 : 04:54:17

"Just use SamC's solution:
WHERE column = CASE WHEN @UserValue = 'Yes' THEN 1 ELSE 0 END"

and

"Select * from table where column = IIF(userinput='Yes',1,0)"

Sorry guys, and yes I know I'm a boring old fart!, but the error checking of these approaches is not good.

These approaches make the assumption that if the value is NOT "Yes" it must of course be "No" is going to lead to submarining of errors - if not now, then at some time in the future. (I do like the UDF_YES_OR_NO Function and its return of NULL for duff-data ... but ujjaval can't use that on SQL-7)

Much better to send this from the Application as a 1 or 0 (rather than "Yes" / "No" / "Somethingelse") as Tara and others have said. But please Please PLEASE validate the data as part of that process - and raise an error, log it, whatever ... just please don't let it silently through.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources