July 17, 2006 at 11:40 am
I was wondering how to set up a couple of stored procedures that I think would be recursive. What I am wanting to do is have a stored procedure that calls a simple select statement that returns one row that contains at maximum 5 ids based on passing in a master ID. The select statement is SELECT Leve1ID, Level2ID, Level3ID, Level4ID, Level5ID from table where listingID = param. All the level ID are in the same record, however some Level ID’s could be null depending on the listingID. What I want to do next is to update a column in another table based on each level ID that is not null. Something like the below
select Level1ID, Level2ID, Level3ID, Level4ID, Level5ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13'
returns:
03 00 00 03 30 00 03 39 00 03 39 23 03 39 23.13
update tbl_MF_Prod_Categories set AT_Active = 'true' where MF_ID = (select Level1ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13')
update tbl_MF_Prod_Categories set AT_Active = 'true' where MF_ID = (select Level2ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13')
update tbl_MF_Prod_Categories set AT_Active = 'true' where MF_ID = (select Level3ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13')
update tbl_MF_Prod_Categories set AT_Active = 'true' where MF_ID = (select Level4ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13')
update tbl_MF_Prod_Categories set AT_Active = 'true' where MF_ID = (select Level5ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13')
So for each levelid that is return I want to call another SP that does the update. Can this be done in SQL Server 2005 and could you pass on your knowledge of how? Oh yes, I am a developer and not a DBA (nor do I pretent do be). Thank you for your assistance.
July 17, 2006 at 12:15 pm
You can do this all in one stored procedure without having to do the first select. Use a JOIN with a derived table so you don't have to do a SELECT...... then an UPDATE based on the results. For example:
update tbl_MF_Prod_Categories
set AT_Active = 'true'
from tbl_MF_Prod_Categories
INNER JOIN (select Level1ID, Level2ID, Level3ID, Level4ID, Level5ID from tbl_MF_Prod_Categories where MF_ID = '03 39 23.13') t
ON tbl_MF_Prod_Categories.MF_ID IN (t.Level1ID, t.Level2ID, t.Level3ID, t.Level4ID, t.Level5ID)
where AT_Active <> 'true'
July 17, 2006 at 12:19 pm
That worked great, thank you.
July 17, 2006 at 12:47 pm
Ok, that worked fine for when the select statement return values for each level, there are times however that some levels may return null. How do I account for those levels that return null values? I wish we had a DBA for this stuff.
July 17, 2006 at 12:53 pm
You don't have to account for it, the JOIN takes care of that. Since you are joining on MF_ID = (list of ID's) the only way you would have a problem is if you allow NULL values in MF_ID. I assumed that MF_ID was a non-null primary key. Is it?
July 17, 2006 at 12:58 pm
Yes, the MF_ID Is the the primary key. I change the MF_ID to a value that only returns Level1ID, Level2ID and Level3ID. Level4ID and Level5ID are NULL. The select statement works but when I run the whole SQL statement it states that 0 records affected. I am assuming it has to do with the two Null values that are being returned.
July 17, 2006 at 1:02 pm
The sql runs fine when I do not include the where AT_Active <> 'true' which does not make sense when it equals null at the moment. I will play around with it some more. Something must be going on with the AT_Active column. I will change it to false and see what happens.
Ok, that seem to do it, now to change that column to something more workable then True or False or null for that matter. Again thanks for your time, I do appreciate it.
July 18, 2006 at 6:39 am
Don't know if this will help with your True/false issues , but it may. Try using NULLIF to help get the proper data.
NULLIF(AT_Active, 'False') <> 'True'
So if the value is NULL make it False and then evaluate it as True/not true.
July 18, 2006 at 9:27 am
I think you meant to say
ISNULL(AT_Active, 'False') <> 'True'
July 18, 2006 at 11:13 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply