Is it possible to insert default data into a column without using column list in insert statement
This question seems to be pointless. But we have an old stored procedure inserting data without a column list and there is a new column. Procedure is not working in test environment giving an error due to missing value for the new column. But it seems working in production. Is there a way to make such a procedure working?
More concretely, let's say we have table A with column a and b; and also a proc with an insert statement like "insert A values (x, y)"
Then column c with a default value has been added to table A. Is there a way to make the insert proc working even though there is no column list?
This is ASE 12.5.3
More concretely, let's say we have table A with column a and b; and also a proc with an insert statement like "insert A values (x, y)"
Then column c with a default value has been added to table A. Is there a way to make the insert proc working even though there is no column list?
This is ASE 12.5.3
"insert into A values (x, y)" ---> This would not work after you have added an additional column with the default values. If you have named the columns like "insert into A(col1, col2) values(x,y), then it would work fine.
You will have to change the code to do the above modification to make it work fine. I think it should be a very minor change all though the dependent procedures, functions, packages etc would get recompiled.
Thanks,
You will have to change the code to do the above modification to make it work fine. I think it should be a very minor change all though the dependent procedures, functions, packages etc would get recompiled.
Thanks,
I'd try dropping and recreating the procedure.
I am guessing that the compiled version of the procedure doesn't know about the new column. If I'm right, drop & recreate proc will fix this.
I am guessing that the compiled version of the procedure doesn't know about the new column. If I'm right, drop & recreate proc will fix this.
Is it possible there's a trigger in production this somehow intercepting and correcting this behavior? It definitely seems like it shouldn't work the way you're describing it.
I'd also consider th other suggestion that the cache version in production doesn't know about the change, but I don't think that wou last too long until it recompiled and sql server realizes there’s a problem.
I'd also consider th other suggestion that the cache version in production doesn't know about the change, but I don't think that wou last too long until it recompiled and sql server realizes there’s a problem.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
To make it clear, I am not trying to find a solution to a problem. It is a mystery I couldn't understand because the proc seems working regardless of the new column. Could it be a bug? Most probably not, because it doesn't work when I load the database into another server.
ASKER
Joe,
Most probably it is because the code in sysprocedures is based on the previous table structure. But, why the proc is not working when database dump has been loaded into another server?
Most probably it is because the code in sysprocedures is based on the previous table structure. But, why the proc is not working when database dump has been loaded into another server?
ASKER
Ryan, I guess what I say proves that it cannot be a trigger because there is an inconsistency between dataservers with the same version. Also this has been happening for years, so cache version has been refreshed so many times.
Does the procedure reference any object in another database? Perhaps dbids, objectids, userids or suserids are not consistent between servers?
I am not trying to find a solution to a problem. It is a mystery I couldn't understand because the proc seems working regardless of the new column. Could it be a bug? Most probably not, because it doesn't work when I load the database into another server.Yes it possible but isn't desirable. Internally the engine will make the match by the order the column were created in the table so if everything matches (values and column data types) it will work. This will only fail when you add a new column and set it as NOT NULL because then you'll need to provide a value for the new column otherwise it will receive the default NULL value.
ASKER
Joe, proc is a very simple one with one insert statement in the same database. There is no clue in the database. I thought about serverwide configuration options. But I couldn't see a clue there either.
alpmoon, what do you want to do with this question?
Delete, Close or leave it opened?
Delete, Close or leave it opened?
ASKER
It might be better to leave it open for a while
Mind that hardly new Experts will come to comment on this question.
Alpoon, you can request the moderators to send an alert for all Experts out there to try to have more attention on this question.
ASKER
It is still a mystery, but I am closing this question. I would write if I find out anything later.
For the default columns it will insert the value once the insert statement will be fired but you need to mention the column list in the stored procedure because the procedure will not know in which columns the data needs to be inserted.