Update query using case incorrect syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query using case incorrect syntax

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-31 : 09:57:17

I am getting a message incorrect syntax near case @language


	UPDATE TAB_ddlfields set EnglishText = @EnglishText,
		EnglishDescription = @EnglishDescription,UpdatedBy = @UserName,

		CASE @language  
			WHEN 'Spanish' THEN  SpanishText = @SpanishText,SpanishDescription = @SpanishDescription
			WHEN 'Catalan' THEN  CatalanText = @CatalanText,CatalanDescription = @CatalanDescription
			ELSE SpanishText = @SpanishText,SpanishDescription = @SpanishDescription
		END

	where pickid = @pickid and fieldlabelkey=@fieldlabelkey

Can you please help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-10-31 : 10:10:02

You can't set the fields inside the case like this, you need to tell the query engine which field you want to update outside the CASE statement.

reddymade
Posting Yak Master

165 Posts

Posted - 2008-10-31 : 10:17:46

I changed it to this way, still getting error:


	UPDATE TAB_ddlLists set EnglishText = @EnglishText,
		EnglishDescription = @EnglishDescription,UpdatedBy = @UserName,

		CASE @language  
			WHEN 'Spanish' THEN  SpanishText = @SpanishText
			WHEN 'Catalan' THEN  CatalanText = @CatalanText
			ELSE SpanishText = @SpanishText
		END LangText,

		CASE @language  
			WHEN 'Spanish' THEN  SpanishDescription = @SpanishDescription
			WHEN 'Catalan' THEN  CatalanDescription = @CatalanDescription
			ELSE SpanishDescription = @SpanishDescription
		END Langdescription

	where pickid = @pickid and fieldlabelkey=@fieldlabelkey

quote:
Originally posted by RickD

You can't set the fields inside the case like this, you need to tell the query engine which field you want to update outside the CASE statement.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-31 : 10:20:39

He meant Field name must be outside CASE.

I don't think using case solves the problem. You might want to dynamically build the SQL based on your language then execute the Update with EXEC(SQLSTR)

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-10-31 : 10:33:59

[code]UPDATE TAB_ddlfields
SET EnglishText = @EnglishText
,EnglishDescription = @EnglishDescription
,UpdatedBy = @UserName
,SpanishText =
CASE
WHEN @language = 'Spanish'
THEN @SpanishText
ELSE SpanishText
END
,SpanishDescription =
CASE
WHEN @language = 'Spanish'
THEN @SpanishDescription
ELSE SpanishDescription
END
,CatalanText =
CASE
WHEN @language = 'Catalan'
THEN @CatalanText
ELSE CatalanText
END
,CatalanDescription =
CASE
WHEN @language = 'Catalan'
THEN @CatalanDescription
ELSE CatalanDescription
END
WHERE pickid = @pickid
AND fieldlabelkey=@fieldlabelkey
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources