Solved

TSQL: how to execute multiple sql statements on a single condition, where later statements depend on execution of the first

Posted on 2011-03-02
3
754 Views
Last Modified: 2012-08-14
see sample code. That does not work, because the statements are executed as a block and the second statement creates an error
-> 'Invalid column name 'person_defaultavailable''

The field isn't yet there. If first the field is created, then I can not use the same condition anymore to check if the other statements need to be executed.




IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
    BEGIN
        ALTER TABLE person ADD person_defaultavailable bit null
        UPDATE person SET person_defaultavailable = 1
        ALTER TABLE person ALTER COLUMN person_defaultavailable bit not null
    END
GO

Open in new window

0
Comment
Question by:sybe
3 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 35017675
TRY this


IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
    BEGIN
        ALTER TABLE person ADD person_defaultavailable bit null
    END    
 GO
       
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
   BEGIN
        UPDATE person SET person_defaultavailable = 1
        ALTER TABLE person ALTER COLUMN person_defaultavailable bit not null
   END
       
       
    
GO

Open in new window

0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35017719
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
    BEGIN
        ALTER TABLE person ADD person_defaultavailable bit null
    END    
 GO
       
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
   BEGIN
		declare @SQL nvarchar(max)
		select @SQL = 'UPDATE person SET person_defaultavailable = 1;
        ALTER TABLE person ALTER COLUMN person_defaultavailable bit not null'
		exec sp_executesql @SQL
   END
       
       
    
GO

Open in new window

0
 
LVL 10

Accepted Solution

by:
John Claes earned 500 total points
ID: 35017735
in what version of SQL are you working?

On my side with 2005/2008 it's working fine.

I'm getting the 1 in the correct column without any errors

see example below

 
create table person
(
	drft int
)
GO
insert into person select 9
insert into person select 8
insert into person select 7
insert into person select 6

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE table_name='person' AND column_name='person_defaultavailable')
    BEGIN
        ALTER TABLE person ADD person_defaultavailable bit null
        UPDATE person SET person_defaultavailable = 1
        ALTER TABLE person ALTER COLUMN person_defaultavailable bit not null
    END
GO 

select * from person
drop table person

Open in new window

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server concatenate fields 10 31
sql server insert 12 30
convert null in sql server 12 31
Increment column based of a FK 8 20
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question