SQL USING WHEN in a SET statment.

Question

4.00/5 (3 votes)

See more:

\]

SQL

This makes total sense to me but it fails with an error.  I am not much of a SQL programmer.  I have researched this error but still curious why this will not work


<pre lang="SQL">
				
BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
WHEN @agency_lob = 'Farm' THEN SET Focus_FarmFocus = 'Y' 
WHEN @agency_lob = 'Commerical' THEN SET Focuse_CommericalFocus = 'Y'
WHERE Focus_FocusAgentCd= @agency_entire_code
END

Posted 4-Jun-14 7:48am

Member 10864572

Add a Solution

Comments

Herman<T>.Instance 4-Jun-14 13:55pm

SET Focus_FarmFocus = Case WHEN @.... OR ..... END
WHERE....
END

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2014-06-04T08:07:00

You can't set columns or not based on conditions: the syntax doesn't allow for it.
What you will have to do is two UPDATES or

SQL

UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
   SET Focus_FarmFocus = CASE WHEN @agency_lob = 'Farm' THEN 'Y' ELSE Focus_FarmFocus END,
       Focuse_CommericalFocus = WHEN @agency_lob = 'Commerical' THEN 'Y' ELSE Focuse_CommericalFocus END
WHERE Focus_FocusAgentCd= @agency_entire_code

member33 · Answer 2 · 2014-06-04T08:14:00

Solution 2

SQL

BEGIN

BEGIN
UPDATE [AdventureWorks].[dbo].[tbl_FocusAgents]
SET Focus_FarmFocus = CASE WHEN   @agency_lob = 'Farm' THEN 'Y' ELSE ''  END,
 Focuse_CommericalFocus = CASE WHEN   @agency_lob = 'Commerical' THEN 'Y' ELSE '' END
WHERE Focus_FocusAgentCd= @agency_entire_code
eND

</pre>

Posted 4-Jun-14 8:14am

member33

Comments

CHill60 4-Jun-14 14:21pm

I disagree. The OP did not imply the ELSE. Your solution will overwrite the values in Focus_FarmFocus when @agency_lob does not equal 'Farm' and in Focus_CommercialFocus when @agency_lob does not equal Commercial. They may have been set to 'Y' or another value by other logic that the OP has not shared with us. Solution 1 by OriginalGriff is the better solution