Click here to Skip to main content
15,038,848 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
There are two boolean checkboxes in UI.
When it is unchecked in the UI, through stored procedure - it should retrieve the count of users assigned with that field in the database table.

Which is better either If-Else or Case Statements in stored procedures?
Also, getting errors in the below tried query.

What I have tried:

ALTER PROCEDURE GetCountOfFieldUsers
@prodId INT,
@approver BIT,
@contractor BIT,

AS
BEGIN
SELECT COUNT(userId),
CASE WHEN @approver = 0
WHERE prodId = @prodId AND approver = 1 GROUP BY prodId
CASE WHEN @contractor = 0
WHERE prodId = @prodId AND contractor = 1 GROUP BY prodId
FROM session_login
END
END
Posted
Updated 1-Jun-21 0:07am

1 solution

You're getting an error because the syntax of your select statement is invalid.
The select should be in format
SQL
SELECT...
FROM ...
WHERE ...
GROUP BY ...

You cannot change the places of different clauses.

What comes to the question, since you're using different columns in the WHERE clause depending on the parameter, I'd recommend using IF...ELSE structure and completely separate statements.
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900