Syntax Validation

Question

Syntax Validation

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 1

well the surest way is to run the code in the environment it is intened to be run but where that environment is producion it should be obvious as to why that is not a good idea.

The vastly safer way which is 99.9% as sure is to run it in a clone of production.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

I think "none of the above" is nearer to a correct answer than any of the other options, because no one of then is adequate on its own.

SET NOEXEC does do a little more than asking for a syntax check using SET PARSEONLY or clicking the green tick button in SSMS (it can be used inside a stored proc or a trigger, for example, to check an addition to existing code, because it is execute time not compile time) but it is nothing like enough on its own. Using in to do the syntax validation in the production environment is somewhat dangerous, to say the least - although if thorough syntax testing, unit testing, and system testing in a clone of the production environment have been done it may be usefful as a check that there are no differences between the production environment and its clone (such as sql server consiguration options) that would cause syntax errors in teh new code. Using it as the only check that your SQL is correct would be pure insanity.

Tom

Toreador SSChampion Points: 11366 More actions · Answer 3

I agree with Tom, that None of the Above is probably the best answer (though I went for running in dev).

NOEXEC is definitely not correct though. Just try this

SET NOEXEC ON

select * from ThisTableDoesNotExist

SET NOEXEC OFF

deepforest SSCommitted Points: 1780 More actions · Answer 4

Toreador (4/14/2014)
I agree with Tom, that None of the Above is probably the best answer (though I went for running in dev).
NOEXEC is definitely not correct though. Just try this
SET NOEXEC ON
select * from ThisTableDoesNotExist
SET NOEXEC OFF

Agree 100%, plus even if you write some custom code check, dynamic sql is near impossible to parse for errors before run time.

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 5

SQL_Hunt

SSC-Dedicated

Points: 33518

December 4, 2014 at 12:48 am

#1762592

Why not option 1?

Thanks.