Help with Sql

@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
vijay11Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ephraim WangoyaConnect With a Mentor Commented:

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)

Open in new window

0
 
Ephraim WangoyaCommented:
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)

Open in new window

0
 
JoeNuvoConnect With a Mentor Commented:

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

Open in new window

0
 
wdosanjosConnect With a Mentor Commented:
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

Open in new window

0
 
vijay11Author Commented:
All the Answers were Excellent .

Thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.