November 5, 2012 at 3:34 pm
I have two sets of tables - one has yearly data and another has monthly data.
The only difference between the two is the additional month column in the latter.
I have a bunch of stored procedures that manipulate the data and create resulting tables.
I modified the SPs to accept a parameter 'M' or 'Y' to run accordingly.
if @prd = 'Y' begin --year
-- logic that create temp tables.
end
else begin --month @prd=M
-- logic
end
The two logic have only one difference - month column.
But at run time, I get an error about the month field even though I am running for year.
The temp table names for both are the same.
Is this allowed? What am I doing wrong?
Any help would be awesome.
Thanks.
November 5, 2012 at 5:05 pm
When compiling a statement, if table referenced in the statement exists then SQL Server will validate the columns in that table, else it will employ 'deferred name resolution.' You are experiencing a problem with the former.
T-SQL is declarative, not interpreted.
I can recommend a couple different courses of action that may work for you:
1. Split your single proc into two procs, refactoring out the common logic into procs that can be called from each. This way you have no shared use of temp tables that are defined differently. (this is the one I would probably choose)
2. Use a single temp table definition in your existing proc and just have the queries that run when @prd = 'Y' ignore the month column, which they would do anyway.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 6, 2012 at 9:48 am
Thanks SSCarpal Tunnel, I used the first option as you suggested.
Greatly appreciate your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply