stored procedure error (using case)
-
I've been reading articles about using case in queries for about an hour now, but I still don't understand whats the problem with this stored procedure:
CREATE PROCEDURE spClientContact
@CustomerID int,
@RecruitingOnly bit = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT ID_Contact, ID_Customer, FirstName + ' ' + LastName AS Name
FROM tblContactPersons
WHERE
CASE @RecruitingOnly
WHEN 1
THEN ID_Customer = @CustomerID AND Position LIKE 'recruiting%'
WHEN 0
THEN ID_Customer = @CustomerID
END
GO
It always says "wrong syntax near '='"
I tried simple CASE, complex CASE, IF statement ...
-
Try this instead:
CREATE PROCEDURE spClientContact
@CustomerID int,
@RecruitingOnly bit = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT
ID_Contact,
ID_Customer,
FirstName + ' ' + LastName AS Name
FROM
tblContactPersons
WHERE
ID_Customer = @CustomerID
and (@RecruitingOnly = 1 and Position LIKE 'recruiting%')
GO
-
Never mind my last post, I missed something. Give me a couple of minutes.
-
Try this code:
CREATE PROCEDURE spClientContact
@CustomerID int,
@RecruitingOnly bit = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT
ID_Contact,
ID_Customer,
FirstName + ' ' + LastName AS Name
FROM
tblContactPersons
WHERE
(@Recruiting = 0
and ID_Customer = @CustomerID)
or (@RecruitingOnly = 1
and ID_Customer =@CustomerID
and Position LIKE 'recruiting%')
GO
-
You basically can't do that.
You can do something like this:
WHERE 1 = CASE
WHEN evaluate something & return 1 or 0
or
WHERE SomeColumn = CASE
WHEN evaluate something THEN some value
You're trying to use the CASE as a control flow and it doesn't do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Ah, very helpful again. Thanks a lot! A lot things are done with such logical operations instead of functions like CASE or IF as I see...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply