HELP: Updating records depending on Stored proc!

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 2000 Forums

SQL Server Development (2000)

HELP: Updating records depending on Stored proc!

Author

Topic

flamz
Starting Member

21 Posts

Posted - 2008-02-23 : 11:55:03

Hello,
I have a stored procedure (lets call it procGetNames) that returns a list rows depending on various constraints and joins to multiple tables. It works fine and it's quite fast.

Now, I need to write an UPDATE query that will set a field in a particular table to the COUNT of the result a that stored procedure.

something like:
UPDATE tblNewTable SET fNameCount = (SELECT Count(*) from procGetNames)

except that that doesn't work...

Any tips?

thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-23 : 13:26:38

Make a temp table to hold the result of stored proc (with structure same as the result set of stored procedure). Let table be #RESULT

INSERT INTO #RESULT
Exec procGetNames ...paramvalues..

UPDATE tblNewTable
SET fNameCount =t.RecCount
FROM (SELECT COUNT(*) AS RecCount FROM #RESULT) t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-25 : 03:11:48

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

johnsql
Posting Yak Master

161 Posts

Posted - 2008-06-19 : 14:22:30

quote:
Originally posted by madhivanan

or
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

Madhivanan

Failing to plan is Planning to fail

I love your online article. Reading through it,it turns out that there is a second way like your Method 2 details. I find it interesting. However, I still have one question for you.

For your Method 2, you directly select from SP via OPENROWSET function. My question is if a stored procedure needs to be passed with some paramters (arguments), then how can you format the last argument in the OPENROWSET function?

Thank you in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-19 : 19:43:14

Can you give me an example of how you want to execute?
You may put entire query in a local variable and run it thru dynamic sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources