Stored Procedure and a new Database Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure and a new Database Field

Author

Topic

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 11:07:41

I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:


CREATE PROCEDURE sp_UpdateProductsTable
@prodName varchar(50),
@prodManufacturer varchar(50),
@prodShortDescrip text,
@prodLongDescrip text,
@prodCatId int,
@prodPrice varchar(6),
@prodSortOrder int,
@prodActive int,
@prodId int
AS
if( @prodId <> 0 )
begin 
      update Products set
      Name = @prodName,
      Manufacturer = @prodManufacturer,
      ShortDescription = @prodShortDescrip,
      LongDescription = @prodLongDescrip,
      CategoryID = @prodCatId,
      Price = @prodPrice,
      SortOrder = @prodSortOrder,
      Active = @prodActive     
      where ID = @prodId; 
     
     select ID as prodId from Products where ID = @prodId
     
end
if( @prodId = 0 )
begin 
      insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID,  SortOrder, Active )
      values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip,  @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
 
      SELECT SCOPE_IDENTITY() AS prodId
end

GO

I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.

Thanks in advance for your help.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 11:28:54

First of all you should try to start using the correct terms:
a database has tables
tables have columns

so you could add columns to a table not fields to a database - but I know what you mean

Why didn't "it work"? did you get an error when you tried? What was the error?

Be One with the Optimizer
TG

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 13:18:25

Ok, so columns and tables then. I did get an error, but I cannot remember what it was. Dammit. I should have copied it down.

I think this was it: Microsoft VBScript runtime error '800a000d' type mismatch

It was the only error message in my search history that was near the stored procedure searches that I did. Basically, I have a column that will not allow a null entry and the stored procedure was overlooking the code I had added. When I returned the insert statement, there was nothing in it pertaining to the new code I had added at all so the table thought I was trying to insert a null into the new column, if that makes sense... At least, thats what I made of this error.

Here is the code I added:


CREATE PROCEDURE sp_UpdateProductsTable
@prodName varchar(50),
@prodManufacturer varchar(50),
@prodShortDescrip text,
@prodLongDescrip text,
@prodCatId int,
@prodPrice varchar(6),
@prodSortOrder int,
@prodActive int,
@prodSpecsPic varchar(50),
@prodId int
AS
if( @prodId <> 0 )
begin 
      update Products set
      Name = @prodName,
      Manufacturer = @prodManufacturer,
      ShortDescription = @prodShortDescrip,
      LongDescription = @prodLongDescrip,
      CategoryID = @prodCatId,
      Price = @prodPrice,
      SortOrder = @prodSortOrder,
      Active = @prodActive,
      SpecsPic = @prodSpecsPic
      where ID = @prodId; 
     
     select ID as prodId from Products where ID = @prodId
     
end
if( @prodId = 0 )
begin 
      insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID,  SortOrder, Active, SpecsPic )
      values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip,  @prodPrice, @prodCatId, @prodSortOrder, @prodActive, @prodSpecsPic );
 
      SELECT SCOPE_IDENTITY() AS prodId
end

GO

When this stored procedure was called the error listed above was given. Hope this helps, as I do not have access to the error at this point because I worked around it as a temporary solution...I thought maybe someone on here would know why I cant just modify an existing stored procedure to work on new table columns that have been added to an already funtioning table/SP relationship.

I also had a stored procedure that only called info from the database - this is probably the more simple one. It was a simple as "SELECT Name, Date, Location FROM TABLENAME WHERE Active = 1" and it worked fine until I added a new column. Then it read "SELECT Name, Date, Location, Image FROM TABLENAME WHERE Active = 1" - after this slight change to the Table and the SP, the new field would NOT return from the database when called by the SP. If I used the same exact query in query analyzer it worked just fine. Or if i typed it out in my ASP page, it would work fine. But when the stored procedure was called, it failed and gave me this error: microsoft VBScript runtime error '800a01a8' Object required: '[undefined]'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 13:27:11

You can modify an existing SP to honor new columns.

- Check to make sure your SP input parameter datatypes match the datatype of the target column.
- try executing the stored procedure manually from a Query Analyzer window (or management studio for 2k5)
- it that is successfull then run sql profiler to capture the exact call you are sending to the database and make sure it is what you expect.
- make sure your application connection is to the sql server to which you have made you changes.

EDIT:
the error you mentioned is not a sql error so your code may have blown up before the db call was made. One other thing to check is that @prodid will always be 0 or <> 0. your code does not account for a <null> value.

Be One with the Optimizer
TG

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 13:32:57

Wow - fast reply. I was updating my post above with more info when you replied, so please see the added text for what I did to troubleshoot.

Thanks for the suggestions - I did the first, second and third and am pretty sure about the 4th, but will double check on that now....

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 13:35:37

I guess I will try to work on this again and see if I can solve it. I will post actual error messages as they are given. maybe it will actually work this time. Thanks for the help, I will let you know what happens.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-28 : 13:39:56

Ok - good luck

one last thing:

quote:

If I used the same exact query in query analyzer it worked just fine. Or if i typed it out in my ASP page, it would work fine. But when the stored procedure was called, it failed and gave me this error: microsoft VBScript runtime error '800a01a8' Object required: '[undefined]'

This is telling me that the problem is in your application code rather than the database code. better double check there to make sure you're handling all the new columns the same way you are handing the existing ones. Sorry, I can't be too much help there...

Be One with the Optimizer
TG

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 13:57:55

No worries. You provided exactly what I needed in order to go back and work this out. Turns out there wasnt (as you stated)anything wrong with the SQL code. It was in the form (I think) as I noticed that there was an issue with a form item missing the ID parameter...so I fixed that and changed all the stuff I needed to change in the SQL again and now it works. So, whatever I did wrong the other day, I didnt do it today.

Thanks for your help!

takuya
Starting Member

6 Posts

Posted - 2007-12-28 : 16:43:38

Sweet. I got them ALL figured out now and not just the one from above. I dont know what the heck I did last week when I tried to do this because it was all straight forward and simple today. I must have been out of it or something.

Thank you for all you help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources