Using CASE with INSERT - Syntax error? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Using CASE with INSERT - Syntax error?

Author

Topic

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-07-15 : 11:35:39

Can anyone help? What am I doing wrong here? This is a portion of a SPROC that inserts an address into the Address table. I was asked to blank out City/State/Zip when the country code is anything other than 'US'. But SSMS complains that the syntax is bad near the word CASE...

...
@addr1 varchar(125),
@addr2 varchar(125),
@addr3 varchar(125),
@addrTo varchar(125),
@city varchar(125),
@state char(2),
@zip varchar(10),
@country varchar(3) = 'US'
AS
...
INSERT INTO Address (
addr_line_1,
addr_line_2,
addr_line_3,
addr_to,
city,
state_cd,
zip_cd,
country_cd,
preferred_fl
)
VALUES (
ltrim(rtrim(@addr1)),
ltrim(rtrim(@addr2)),
ltrim(rtrim(@addr3)),
ltrim(rtrim(@addrto)),
CASE @country WHEN 'US' THEN ltrim(rtrim(@city)) ELSE '' END,
CASE @country WHEN 'US' THEN ltrim(rtrim(@state)) ELSE '' END,
CASE @country WHEN 'US' THEN ltrim(rtrim(@zip)) ELSE '' END,
ltrim(rtrim(@country)),
ltrim(rtrim(@pref)))

-Todd Davis

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-15 : 11:40:38

try below:

CASE WHEN @country = 'US' THEN ltrim(rtrim(@city)) ELSE '' END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 11:41:08

[code]@addr1 varchar(125),
@addr2 varchar(125),
@addr3 varchar(125),
@addrTo varchar(125),
@city varchar(125),
@state char(2),
@zip varchar(10),
@country varchar(3) = 'US'
AS
...
INSERT INTO Address (
addr_line_1,
addr_line_2,
addr_line_3,
addr_to,
city,
state_cd,
zip_cd,
country_cd,
preferred_fl
)
SELECT
ltrim(rtrim(@addr1)),
ltrim(rtrim(@addr2)),
ltrim(rtrim(@addr3)),
ltrim(rtrim(@addrto)),
CASE @country WHEN 'US' THEN ltrim(rtrim(@city)) ELSE '' END,
CASE @country WHEN 'US' THEN ltrim(rtrim(@state)) ELSE '' END,
CASE @country WHEN 'US' THEN ltrim(rtrim(@zip)) ELSE '' END,
ltrim(rtrim(@country)),
ltrim(rtrim(@pref))
...[/code]

toddhd
Yak Posting Veteran

55 Posts

Posted - 2008-07-15 : 11:43:58

quote:
Originally posted by cvipin

try below:

CASE WHEN @country = 'US' THEN ltrim(rtrim(@city)) ELSE '' END

Yup, that did it. Thanks. Not enough caffeine today I suppose...

-Todd Davis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources