September 2, 2009 at 7:28 am
Hi,
I have to perform a select on a table and return Uids where profileid is null and update the profileid to a certain value.
I then have to send to a certain sp all the Uids returned in the first query in addition to some other hard coded values. How can i write a query that will update the first table and also send values to the sp for each Uid returned?
This is what i came up with so far but i am not sure how to pass the CPU_id to the sp
CREATE TABLE #portalusers(
CPU_id int )
INSERT INTO #portalusers (CPU_id)
select CPU_id from dbo.tb_knbCPortalUser
where CPU_extprofileID is null and CPU_usertype is null
DECLARE @counter int
DECLARE @MaxCount int
declare @countfilter int
declare @i int
set @counter=1
set @countfilter=(select count (CPU_id) from #portalusers)
while @counter <= @countfilter
begin
exec sp_updateUsers @CPU_id
end
drop table #portalusers
Thanks for the hellp
September 2, 2009 at 7:57 am
collie,
This might help:
CREATE TABLE #portalusers
(
IDBIGINT IDENTITY(1,1),
CPU_idINT
)
.....
DECLARE @IDBIGINT
DECLARE @MaxCountINT
DECLARE @countfilterINT
DECLARE @iINT
SET@ID = 0
SELECT@ID = MIN(ID) FROM #portalusers
WHILE ISNULL(@ID, 0) > 0
BEGIN
SELECT@CPU_id = CPU_id
FROM#portalusers
WHEREID = @ID
GO
EXEC sp_updateUsers @CPU_id
GO
SELECT@ID = MIN(ID)
FROM#portalusers
WHEREID > @ID
END
ianhoc
September 2, 2009 at 9:15 pm
Actually, you could probably dispense with the loop and the temp. table by outputting the CpuIds as xml to an xml variable, passing it as a parameter to sp_updateUsers and then shredding it as part of a set based operation with the xml type nodes method.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply