Call sp a few times - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Call sp a few times

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-08-31 : 10:40:58

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.

This is fine.
However, there is a sp that is in place that i can't change. I have to send to that parameter 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?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-31 : 14:06:57

It looks like you will have to perform a loop.

OR

Here's an idea. Just because you can't change the SP it doesn't mean you can't grab the logic in it and use it in your query rather than call it, right?

An infinite universe is the ultimate cartesian product.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-01 : 11:24:49

Thanks for the reply.

I prefer the loop option :-)
How can i send the ids in a loop to the sp?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-09-01 : 19:13:55

Here's an approach:

1) SELECT your data into a temp table or table variable; include an identity column.
2) Loop
3) Pull the "next" data based on the identity column value (WHERE ident = @LoopCnt)
4) Call the stored procedure using the current value(s)
5) Increment the loop counter
6) Return the original data set from the temp table

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-09-02 : 07:13:20

Hi,

Thanks for the repy.

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

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources