September 7, 2006 at 1:16 pm
I know this sounds weird, but I'm looking to see if you can create a stored procedure without having it be syntax checked. For example, if the SP references a column that does not yet exist, or no longer exists, I would like to still be able to create the stored procedure.
What prompts the situation is that I have a system that creates a backup of a SP under a different name prior to working on the SP. So if you, for example, drop a column and forget to remove a reference from a SP prior to actually removing the column, then the backup creation fails due to an invalid column reference. I know I could rename the SP, but the two problems with that approach are that the real version would no longer exist and the renamed version would still have the normal name internal to its definition which is a little dangerous and confusing.
I'm guessing it can't be done, and couldn't find anything in my searches, but figured I'd ask here before giving up.
September 7, 2006 at 3:15 pm
Try with dynamic SQL
September 7, 2006 at 7:14 pm
How is that gonna avoid the server validating the sp??
September 8, 2006 at 6:47 am
The intent isn't for the backup version to be executable. In fact, prior to making modifications it already is broken. I just "need", so to speak, a backup of the original for change control and comparison purposes. I do actually use dynamic SQL to apply the CREATE. Since the intent is to both do this in an automated fashion and to keep the backup in an unaltered state for comparison purposes, I don't want to change the actual content of the SP into dynamic SQL (which is what would avoid validation per Nagabhushanam's suggestion).
So far I have 2 options to my knowledge. One is to just let it fail and fumble through those situations somehow manually (put the column back momentarily for example). The other is to export the SP to a file using COM+. So unless someone knows of a way to tell SQL Server to just blindly create a SP/UDF I'll have to take the backup outside of SQL Server into the file system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply