lots of ifs - 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

Transact-SQL (2000)

lots of ifs

Author

Topic

programmed2live
Starting Member

1 Post

Posted - 2007-08-17 : 10:15:44

I've got about 50 columns of type bit. I need take them and return a 1 if they're all 1s and a 0 if any of them are not a one.

I came up with this:

select * from table where field1=1 and field2=1 and field3=1 ... (30 minutes later) field50=1 and keyfield=@keyvalue

select @@rowcount as mystatusvalue

but I'm sure this isn't the best way to do this. Any suggestions?

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-17 : 10:22:08

You know the number of columns, why not add the values and compare that to the number of fields in a case statement.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-17 : 10:24:57

You can use the & operator for this:

SELECT field1 & field2 & field3 &.... and so on FROM YourTable

If all fields are 1, it will return 1, else it will return a 0.

SQL Server Helper
http://www.sql-server-helper.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-17 : 10:26:59

[code]
select
*
from
MyTable
where
case
when col_01 <> 1 then 0
when col_02 <> 1 then 0
when col_03 <> 1 then 0
...
...
when col_50 <> 1 then 0
else 1
end = 1

[/code]

CODO ERGO SUM

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-08-17 : 10:32:15

Declare @ord int, @field varchar(255)
, @sql nvarchar(4000)

Select Top 1 @ord = Ordinal_Position
, @field = Column_Name
From Information_schema.columns
Where table_name = 'YourTable'
Order By Ordinal_Position

Set @sql = 'Select Case When '

While @@RowCount <> 0
Begin
Set @sql = @sql + @field + ' & '

Select Top 1 @ord = Ordinal_Position
, @field = Column_Name
From Information_schema.columns
Where table_name = 'YourTable'
And Ordinal_Position > @ord
Order By Ordinal_Position

End

Set @sql = Left(@sql, Len(@sql) - 1) + ' = 1 Then 1 Else 0 End As BitCheck
From YourTable'

Exec sp_ExecuteSql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources