Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with Sql

Posted on 2011-03-16
Medium Priority
266 Views
@a  int  ,@b  int  , @c  int  , @d   int

table

id   name   a    b   c    d

a,b,c,d have values of 0 or 1  only

@a belongs to column a , @b belongs to column b , @c belongs to column c , @d belongs to column d

@a,@b,@c and @d are populated when I click on check box in the front end

If only @a is checked then @a will be 1 and all the variables will be 0 . I want the value from table when  a = 1 .If only @a is 1
ans same logic for all variable if only 1 variable is checked in the front end

if a user checks more than 1(ex @b and @d)  than I want all  the values in which column b is 1 or column d is 1 (Basically b= 1 or d=1)

If the user don't check any variable I want all the data from table.

At this point I can only think of 'if' statement can this be done in any other method
0
Question by:vijay11
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 32

Accepted Solution

Ephraim Wangoya earned 668 total points
ID: 35152623

This is better handled in a stored procedure but here is the sequence you could use

``````declare @sql varchar(max)
declare @a  int  ,@b  int  , @c  int  , @d   int
set @sql = '';

if @a = 1
set @sql = 'WHERE a = 1'

if @b = 1
if @sql <> ''
set @sql = 'AND b = 1'
else
set @sql = 'WHERE b = 1'

if @c = 1
if @sql <> ''
set @sql = 'AND c = 1'
else
set @sql = 'WHERE c = 1'

if @d = 1
if @sql <> ''
set @sql = 'AND d = 1'
else
set @sql = 'WHERE d = 1'

set @sql = 'SELECT * FROM TABLEA ' + @sql

exec(@sql)
``````
0

LVL 32

Expert Comment

ID: 35152631
You need spaces in the filter
``````declare @sql varchar(max)
declare @a  int  ,@b  int  , @c  int  , @d   int
set @sql = '';

if @a = 1
set @sql = ' WHERE a = 1'

if @b = 1
if @sql <> ''
set @sql = ' AND b = 1'
else
set @sql = ' WHERE b = 1'

if @c = 1
if @sql <> ''
set @sql = ' AND c = 1'
else
set @sql = ' WHERE c = 1'

if @d = 1
if @sql <> ''
set @sql = ' AND d = 1'
else
set @sql = ' WHERE d = 1'

set @sql = 'SELECT * FROM TABLEA ' + @sql

exec(@sql)
``````
0

LVL 11

Assisted Solution

JoeNuvo earned 668 total points
ID: 35153434

``````Declare @s int
SET @s = @a + @b + @c + @d

SELECT * FROM table
WHERE
a = CASE WHEN @s = 0 THEN a WHEN @a = 1 THEN 1 ELSE 2 END
OR	b = CASE WHEN @s = 0 THEN b WHEN @b = 1 THEN 1 ELSE 2 END
OR	c = CASE WHEN @s = 0 THEN c WHEN @c = 1 THEN 1 ELSE 2 END
OR	d = CASE WHEN @s = 0 THEN d WHEN @d = 1 THEN 1 ELSE 2 END
``````
0

LVL 23

Assisted Solution

wdosanjos earned 664 total points
ID: 35153614
You can try something like this:

``````select *
from YourTable t
where t.a = (2 - @a)
or t.b = (2 - @b)
or t.c = (2 - @c)
or t.d = (2 - @d)
or (@a + @b + @c + @d) = 0
``````
0

Author Closing Comment

ID: 35156217
All the Answers were Excellent .

Thanks for the help
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
###### Suggested Courses
Course of the Month11 days, 4 hours left to enroll