Solved

Help with Sql

Posted on 2011-03-16
5
244 Views
Last Modified: 2012-05-11
@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
Comment
Question by:vijay11
5 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 167 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)

Open in new window

0
 
LVL 32

Expert Comment

by:ewangoya
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)

Open in new window

0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 167 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

Open in new window

0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 166 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

Open in new window

0
 

Author Closing Comment

by:vijay11
ID: 35156217
All the Answers were Excellent .

Thanks for the help
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question