Sql Validation

Question

Sql Validation

JKSQL

SSCrazy Eights

Points: 8323
More actions
December 15, 2008 at 8:42 am

#126530

I have a script that works in versions.
example
if @i = 1
BEGIN
END
if @i = 2
BEGIN
END
in a block of code I had to add a field for a cookie crumb to be used later. That worked fine. Then after the script "Version" ran I dropped the field. Now that I am in a new version say version 3 the sql compiler breaks in version two because it is trying to use that field to join on. is there a way around that or do I need to rewrite the script without the additional field?
thanks for the help

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 1

If I understand the problem, then I'd suggest instead of having three queries for the three version, have three procedures and then, depending on the version, using your IF statement, you can execute the appropriate proc. That way, if there are fields that exist in version 1, but not in version 3 or vice versa, the procedure handles the issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

steveb. SSC-Forever Points: 46733 More actions · Answer 2

It makess the code hard to maintain and read if you it include references to objects that no longer exsit so I would comment out or delete the old code that is no longer relevant

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 3

i did not explain myself well. Sorry. I am just going to store the cookie crumb in a temp table. I can not use sp's and also can not comment code out. It is an upgradeable script.

Thanks

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 4

Then you're just a little bit stuck. You can't refer to objects that don't exist in the query, even if it's just in an IF statement. Why couldn't you use procedures? It's all running on the server.

You could, and I don't recommend going this route, use dynamic sql, build the string and execute it rather than have the ready to run query.

BTW, if you're upgrading the underlying structure or code, version the query in source control and build the appropriate database to the version of the query. That's a much more appropriate management method.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning