stored procedure within a stored procedure

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

Transact-SQL (2008)

stored procedure within a stored procedure

Author

Topic

sconard
Starting Member

18 Posts

Posted - 2012-09-26 : 14:35:33

I have done this before but cannot discern why this does not work.

From a query window opened from the db I write

update table1 set field1 = (spStoredProcedure1 field2)

spStoredProcedure1 (within same db) is passed t1.field2 and returns a value

The error message states syntax. I have tried exec and various syntax but no solution.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-26 : 14:42:51

Are you trying to capture the return value or a value from the result set from spStoredProcedure1 ?

sconard
Starting Member

18 Posts

Posted - 2012-09-26 : 14:48:21

yes as the update is trying to set field1 to result

quote:
Originally posted by Lamprey

Are you trying to capture the return value or a value from the result set from spStoredProcedure1 ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-26 : 14:56:10

Put the return or output value into a variable and then use the variable for the update command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sconard
Starting Member

18 Posts

Posted - 2012-09-26 : 14:59:13

The return value varies based on the input value of the update query. Updating an entire table of values in one field.

IE update table1 set field1 = field2 will update an entire table of values for field1.

quote:
Originally posted by tkizer

Put the return or output value into a variable and then use the variable for the update command.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-26 : 15:24:46

You could rewrite the stored procedure as a user-defined function (provided it doesn't make any permanent system changes like inserting/deleting/updating another table) and use it like this:

update table1 set field1 = dbo.myUDF(field2)

If you post the code of the stored procedure we could help with the translation.

sconard
Starting Member

18 Posts

Posted - 2012-09-27 : 10:12:29

And I should have thought of using a function. Worked perfect as

UPDATE table1
SET field1 = function1(field2)

this may not have been the most efficient method but it was a one off.
Thanks!

quote:
Originally posted by robvolk

You could rewrite the stored procedure as a user-defined function (provided it doesn't make any permanent system changes like inserting/deleting/updating another table) and use it like this:
update table1 set field1 = dbo.myUDF(field2)
If you post the code of the stored procedure we could help with the translation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources