What's wrong with this query? - 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)

What's wrong with this query?

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-19 : 08:35:31

THe following query generates, Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ',' errors.

Can anyone tell me what I am missing?

EXECUTE sp_MSforeachtable '
select if(COL_LENGTH(''?'',''rn_create_user'') > 0, rn_create_user, rn_create_user)
from ? as o
where
not exists(select * from users u
where (u.users_id = o.rn_create_user)
)';

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-19 : 08:50:01

Try this

EXECUTE sp_MSforeachtable '
select case when (COL_LENGTH(''?'',''rn_create_user'')) > 0 then rn_create_user else rn_create_user end
from ? as o
where
not exists(select * from users u
where (u.users_id = o.rn_create_user)
)';

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 08:51:03

A lot of things.

IF is procedural, not inline.
Change to CASE.

And why do you not pay attention to the solutions given to you before?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102758
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103043

SELECT		TABLE_NAME
FROM		INFORMATION_SCHEMA.COLUMNS
WHERE		COLUMN_NAME IN ('rn_create_user', 'rn_edit_user')
GROUP BY	TABLE_NAME
HAVING		COUNT(DISTINCT COLUMN_NAME) = 2
ORDER BY	TABLE_NAME

And in this specific code of yours, what is the purpose of even query for the length of the column rn_create_user when you output the same column regardless of "if" statement?

E 12°55'05.25"
N 56°04'39.16"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2008-05-27 : 07:30:08

Hey Peso, obviously if they made sense to me I would not be asking for assistance again. Thanks for your suggestion to pay attention, let me get right on that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources