Passing parameters in a SQL query

Question

I am passing where clause parameter values from a program however when I am sending at least one one value I get the result however if I am sending all parameters as null no output is coming, i want a query such that if all parameters are sent as null will display all the records in the database

following is the query which retrieves values if one condition is satisfied

SELECT * 
  FROM STUDENT
  LEFT JOIN COURSE
    ON STUDENT.COURSE_ID = COURSE.COURSE_ID
 WHERE STUDENT.STD_ID = null
   OR STUDENT.STD_NAME = null
   OR STUDENT.STD_START_DATE = null
   OR STUDENT.STD_END_DATE = null
   OR STUDENT.STD_GENDER = null
   OR STUDENT.COURSE_ID = null;

STD_START_DATE =null does not work you need to use STD_START_DATE IS null — user330315
– user330315, Commented Aug 3, 2015 at 10:04
How are you passing the variables across from your program? I don't see any paramters in your example sql statement. — Boneist
– Boneist, Commented Aug 3, 2015 at 10:57

Pரதீப் · Accepted Answer · 2015-08-03 10:10:53Z

2

Try below,

SELECT *
FROM   STUDENT
       LEFT JOIN COURSE
              ON STUDENT.COURSE_ID = COURSE.COURSE_ID
WHERE  STUDENT.STD_ID IS NULL
        OR STUDENT.STD_NAME IS NULL
        OR STUDENT.STD_START_DATE IS NULL
        OR STUDENT.STD_END_DATE IS NULL
        OR STUDENT.STD_GENDER IS NULL
        OR STUDENT.COURSE_ID IS NULL

edited Aug 3, 2015 at 10:10

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

answered Aug 3, 2015 at 10:05

psmydin

236 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2218550 Over a year ago

actually my question is that i require a query such that if all values go as null as assigned in the above query i want another query to execute which fetches all the data without any condition like SELECT * FROM STUDENT LEFT JOIN COURSE ON STUDENT.COURSE_ID = COURSE.COURSE_ID

user2218550 · Accepted Answer · 2015-08-03 11:32:01Z

0

Got tHe solution:

SELECT *
FROM
STUDENT S,COURSE C
WHERE
S.STD_ID like '%'||null||'%' and S.STD_NAME like '%'||null ||'%' and S.STD_START_DATE like '%'||null ||'%' and S.STD_END_DATE like '%'||null ||'%' and S.STD_GENDER like '%'||null||'%'
and S.COURSE_ID like '%'||null||'%' and S.COURSE_ID=C.COURSE_ID

answered Aug 3, 2015 at 11:32

user2218550

1471 gold badge2 silver badges11 bronze badges

Collectives™ on Stack Overflow

Passing parameters in a SQL query

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related