IF exists statement - SQL Server Forums

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)

IF exists statement

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-01-14 : 14:20:24

I am trying to base the next few lines of code a condition. The condition is if any of the records in the table have a length of 16 then execute code that will pad the fields with leading zeros.

I am getting an error says there is a syntax error in the following statement

If Exists
(select * from Lost_Data where len([Acct Num] = 16)
UPDATE Lost _Data SET [Acct Number] = '0000' + [Acct Number]

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-14 : 14:29:55

Your code is missing the closing paranthesis ) on the len call

you typed: len([Acct Num]=16)
it should be len([Acct_Num])=16)

at least thats what I think..

r&r

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-14 : 14:46:58

Sometimes it's the little things that get away from us

Trudye
Posting Yak Master

169 Posts

Posted - 2009-01-15 : 06:25:27

Thanks Guys for responding so quickly. You were right about the right paren, I placed it behing the value 16
If Exists
(select * from Lost_Data where len([Acct Num] = '16'))
UPDATE Lost _Data SET [Acct Number] = '0000' + [Acct Number]

Now I'm getting error msg: Incorrect syntax near '='.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-15 : 06:29:19

quote:
Originally posted by Trudye

Thanks Guys for responding so quickly. You were right about the right paren, I placed it behing the value 16
If Exists
(select * from Lost_Data where len([Acct Num]) = '16')
UPDATE Lost _Data SET [Acct Number] = '0000' + [Acct Number]

Now I'm getting error msg: Incorrect syntax near '='.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 06:52:55

[code]IF EXISTS (SELECT * FROM LOST_DATA WHERE LEN([Acct Num]) < 16)
UPDATE Lost _Data
SET [Acct Number] = RIGHT('0000000000000000' + [Acct Number], 16)
WHERE LEN([Acct Num]) < 16[/code]

E 12°55'05.63"
N 56°04'39.26"

Trudye
Posting Yak Master

169 Posts

Posted - 2009-01-15 : 09:39:23

Thanks everyone for pitching in, revdnrdy your response was on the money.

Have a Great Day Everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources