CODEWITHSUNDEEP
sqlsql-server
ConnorU
ConnorU

Reputation: 1409

SQL-Server: using an if in the where operator

I'm creating a stored procedure that gets values from a table that stores projects funding (their id, their goal and what they've received so far) And the proc would return either the project ID's that have been fully funded or those that haven't given a parameter @QueryType, but I don't know how to put an if condition in a where, I've only ever used them in the select portion of a statement.

This is what I have so far but it just gives me INCORRECT SYNTAX on the first case:

--create proc Projects.GetFundedProjects -- Projects that have reached their goal
create proc Projects.GetFundedProjects
(@QueryType int
)
as
begin
select * from Stats.FundedProjectsToday
CASE 
WHEN @QueryType = 1 -- Projects that reached their goal
THEN
     where AUReceived=>ProjectGoal
WHEN @QueryType = 2 -- Projects that have not been funded
THEN 
    where AUReceived<ProjectGoal
end --end the case
end -- end the proc

Upvotes: 0

Views: 37

Answers (2)

Roland Bouman
Roland Bouman

Reputation: 31961

The WHERE keyword can appear only once and after the FROM clause.

SELECT * 
FROM   Stats.FundedProjectsToday
WHERE  CASE 
         WHEN @QueryType = 1 -- Projects that reached their goal
           THEN AUReceived >= ProjectGoal
         WHEN @QueryType = 2 -- Projects that have not been funded
           THEN AUReceived < ProjectGoal
       END

Alternatively

SELECT * 
FROM   Stats.FundedProjectsToday
WHERE  CASE @QueryType
         WHEN 1 -- Projects that reached their goal
           THEN AUReceived >= ProjectGoal
         WHEN 2 -- Projects that have not been funded
           THEN AUReceived < ProjectGoal
       END

Would probably be written sooner as

SELECT * 
FROM Stats.FundedProjectsToday
WHERE (@QueryType = 1 AND AUReceived >= ProjectGoal)
OR    (@QueryType = 2 AND AUReceived < ProjectGoal)

or as

SELECT * 
FROM Stats.FundedProjectsToday
WHERE @QueryType = 1 
AND   AUReceived >= ProjectGoal
UNION ALL
SELECT * 
FROM Stats.FundedProjectsToday
WHERE @QueryType = 2 
AND   AUReceived < ProjectGoal

Upvotes: 1

potashin
potashin

Reputation: 44581

Use mutually exclusive conditions for each @QueryType value:

select * 
from Stats.FundedProjectsToday
where (@QueryType = 1 and AUReceived >= ProjectGoal)
   or (@QueryType = 2 and AUReceived < ProjectGoal)

By the way, greater or equal operator is written as >=, not =>.

Upvotes: 1

Related Questions