CASE statements based on syscolumns

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)

CASE statements based on syscolumns

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-02-03 : 16:39:46

Hello All,

I have to update a column in my temp table based upon the syscolumns exist for each table.

I am writing a dynamic SQL. this sql should update column where_clause with

value '2' if a table contains fields1 and field2 as name in syscolumns table.

value '1' if a table contains fields1 but not field2 as name in syscolumns table.

value '0' if a table does not contain any in syscolumns table.

Here is my code:


INSERT INTO InsertSysarticles
select ' use ' + Database2 + ' UPDATE a SET where_clause =
 CASE WHEN lower (col1.name ) =  ''field1'' AND lower (col1.name ) =  ''field2''  THEN ''2''
WHEN lower (col1.name ) =  ''field1'' AND lower ( col1.name ) NOT LIKE ''field2'' THEN ''1''
WHEN lower (col1.name ) NOT LIKE ''field1'' AND lower ( col1.name ) NOT LIKE ''field2'' THEN ''0''
END)
from [Repl].dbo.ReplArt2 a inner join '
		+ '' + SubsServer + '' + '.' 
		+ '' + SubsDatabase + '' 
		+ '.dbo.sysobjects obj1 '
		+ ' on ( obj1.name collate Latin1_General_CI_AS = a.TableName and type = ''U'' ) '
		+ ' inner join '
		+ '' + SubsServer + '' + '.'
		+ '' + SubsDatabase + ''
		+ '.dbo.syscolumns col1 on ( obj1.id = col1.id ) '  
		+ ' where a.SiteName = ' 
		+ '''' + SiteName + '''' 
	FROM [Repl].dbo.ReplDatabases WHERE Active = 1

The first CASE condition does not seem to be working.

Can anyone help me with this?

Thanks,
-S

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-03 : 17:00:16

quote:
WHEN lower (col1.name ) = ''field1'' AND lower (col1.name ) = ''field2''

col1.name CANNOT contain the value 'field1' AND also the value 'field2'

quote:
WHEN lower (col1.name ) = ''field1'' AND lower ( col1.name ) NOT LIKE ''field2''

If col1.name contains the value 'field1' your NOT LIKE is always true otherwise your NOT LIKE is needless

I would kindly recommend you to learn about AND and OR

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-03 : 17:26:04

Use EXISTS and NOT EXISTS instead of AND/OR in the CASE statement.


INSERT INTO InsertSysarticles
select ' use ' + Database2 + ' UPDATE a SET where_clause =
	case when  
		exists (select 1 from sys.columns where name = ''field1'') and  
		exists (select 1 from sys.columns where name = ''field2'')  then ''2''
		when
		exists (select 1 from sys.columns where name = ''field1'') and  
		not exists (select 1 from sys.columns where name = ''field2'')  then ''1''
		else ''0''  -- <What about the case where field2 exists and field1 does not>
	end
from [Repl].dbo.ReplArt2 a inner join '
		+ '' + SubsServer + '' + '.' 
		+ '' + SubsDatabase + '' 
		+ '.dbo.sysobjects obj1 '
		+ ' on ( obj1.name collate Latin1_General_CI_AS = a.TableName and type = ''U'' ) '
		+ ' inner join '
		+ '' + SubsServer + '' + '.'
		+ '' + SubsDatabase + ''
		+ '.dbo.syscolumns col1 on ( obj1.id = col1.id ) '  
		+ ' where a.SiteName = ' 
		+ '''' + SiteName + '''' 
	FROM [Repl].dbo.ReplDatabases WHERE Active = 1

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-02-03 : 22:56:21

Thanks for the reply.

I know that I can not write two different values for the same column with AND. But could you please tell me the correct way to do it.

Thanks,
-S

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 03:15:52

can i ask what you're trying to achieve here?

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-02-04 : 14:19:31

I have to write a script which would give me the names of the tables which have unsynchronized data across publisher and subscribers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources