MSSQL server syntax checking, targeting multiple environments in one script

Question

This is a really irritating problem to have that springs from poor process, I know, but it's a problem I am currently saddled with none the less.

I descended into madness when the schema changed for half of our environments but not the other half. One half now have an extra field. So there's a basic insertion script that needs to run in both environments

ex:

insert into Tbl values ('foo')

but on the other environments it needs to be

insert into Tbl values ('foo','bar')

I tried solving this with an 'if' since we have a table that identifies what environment you are in

if (select name from environment) in ('local','local2')  
insert into Tbl values ('foo')  
else  
insert into Tbl values ('foo, 'bar')

Unfortunately, MS SQL server syntax checks my entire script, and notes that the else portion doesn't match the schema of the current table.

How can I handle this? The requirements are basically: This has to be a single script that runs in both environments. Is this possible?

Thank you for your time.

TToni · Accepted Answer · 2010-12-17 15:44:13Z

3

Due to the syntax checking you might need to use dynamic SQL:

if (select name from environment) in ('local','local2')
  execute('insert into Tbl values (''foo'')')
else
  execute('insert into Tbl values (''foo'', ''bar'')')

The syntax gets checked only when the inner statement is executed.

answered Dec 17, 2010 at 15:44

TToni

9,4011 gold badge33 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe Stefanelli · Accepted Answer · 2010-12-17 15:48:18Z

2

You could try using dynamic sql to circumvent the syntax checking:

if (select name from environment) in ('local','local2')
exec sp_executesql N'insert into Tbl values (''foo'')'
else
exec sp_executesql N'insert into Tbl values (''foo'', ''bar'')'

answered Dec 17, 2010 at 15:48

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

Collectives™ on Stack Overflow

MSSQL server syntax checking, targeting multiple environments in one script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related