December 9, 2002 at 3:27 am
Iam trying to do an update to a database with the code below, the code will work in generating a single record (if i exclude the while statement) but i need it to create multiple new recordss based on the while statement (oor something similar) i have at the bottom of the code. It needs to create updates for every record that has a parentguid that matches the one specified in the code. I have tryed doing the code as
EXEC ......
WHERE.....
but this doesn't work any ideas on how is best to do this?
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @GUID UNIQUEIDENTIFIER
DECLARE @PARENTGUID UNIQUEIDENTIFIER
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
WHILE @PARENTGUID = '8CF850AD-2026-411B-AABE-BF1584624EB3'
December 9, 2002 at 3:49 am
i don't know what the stored procedure usp_itemsdb_insertgroup does. You have partguid as input parameter so the stored procedure would look something like this:
update table x
set guid=@guid,code=@code
where parentguid=@parentguid
then in you code you just call this stored procedure like this:
EXEC DSDBA.usp_ItemsDB_InsertGroup @GUID, @CODE, 'JMB', @PARENTGUID, 1
The where is put in the stored procedure
December 9, 2002 at 4:01 am
The stored procedure im using is below, i'm unclear as to where i would put the while statement into here, also is there no way i can do the while or where statement from my code rather than the stored procedure as the stored procedure is used by other functions.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure dsdba.usp_ItemsDB_InsertGroup (@GUID uniqueidentifier,
@Code varchar(12),
@Description varchar(60),
@ParentGUID uniqueidentifier,
@SequenceNo integer) as
begin
set nocount on
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @NextHierarchyID varchar(10)
--Work out the next Hierarchy ID number
--This will lock the table to prevent other users
--grabbing the same number as us.
update DSDBA.NextItemGroupHierarchyPostFix
set NextNumber = NextNumber + 1
--Read back the new number.
select @NextHierarchyID = cast(NextNumber as varchar(10))
from DSDBA.NextItemGroupHierarchyPostFix
--Create the new group, check to see if it has a parent group.
if @ParentGUID = @GUID or @ParentGUID is null
insert into dsdba.ItemGroups
(GUID, Code, [Description], Hierarchy, GroupLevel, ParentGUID, SequenceNo)
values (@GUID,
@Code,
@Description,
'.' + @NextHierarchyID + '.',
1,
@GUID,
1)
else
insert into dsdba.ItemGroups
(GUID, Code, [Description], Hierarchy, GroupLevel, ParentGUID, SequenceNo)
select @GUID,
@Code,
@Description,
IG.Hierarchy + @NextHierarchyID + '.',
IG.GroupLevel + 1,
@ParentGUID,
@SequenceNo
from dsdba.ItemGroups IG
where IG.GUID = @ParentGUID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
December 9, 2002 at 4:16 am
Ok, i would create a new stored procedure that does your update that would be something like this:
create procedure usp_updateItems
(
--input parameter are put here
)
as
--local variables
DECLARE @PREFIX VARCHAR(6)
DECLARE @CODE VARCHAR(8)
DECLARE @PARENTGUID UNIQUEIDENTIFIER
DECLARE @GUID uniqueidentifier
--if not always 'just' then move to input parameter
SET @PREFIX = 'JUST'
SELECT @PARENTGUID = GUID FROM DSDBA.iTEMGROUPS WHERE CODE = @PREFIX
SET @CODE = @PREFIX + '02'
SELECT @GUID = NEWID()
update dsdba.ItemGroups
set GUID = @GUID,
Code = @code,
where parentguid=@parentguid
go
December 9, 2002 at 8:11 am
DO you think it is possible to do without calling a new stored procedure because it needs to execute everything thats done in the existing stored procedure but i'm conscious about changing the format of the existing stored procedure because other functions execute it.
I've tryed using an IF statement so that
if parentguid = '........'
begin
......
......
......
exec ......
end
but this doesn't seem to do anything as it doesn't generate errors but doesn't upload anything to the database.
December 9, 2002 at 8:42 am
Can you use sets and avoid a while loop? I have some processes that insert matching records for tables to keep them synced up. I use a single insert statement that joins the 2 tables.
eg
create table t1
(t1ID int
... other attrs
)
create table t2
(t2ID int
... other attrs
)
insert t2 (t2ID, <other cols>)
select t1ID
from t1
left join t2 on t1ID=t2ID
where t2ID is null
Would this work for you?
December 9, 2002 at 8:48 am
All the data comes from the same table. It needs to read from the table called dsdba.itemgroups to find all records with the same parentguid and it is into this table that the updated records go also. So i'm not sure if this method would work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply