exec sproc from another sproc
-
Hi All,
I'm trying to execute one sproc from another together but keep getting snagged with syntax errors (msg170) and can't figure out where I'm going wrong, here they are by themselves:
1.
CREATE PROCEDURE dbo.getModuleTitle
@ModuleID int
AS
SELECT dbo.Modules.ModuleTitle
FROM dbo.Modules
WHERE dbo.Modules.ModuleID = @ModuleID
GO
2.
CREATE PROCEDURE dbo.UpdateMyText
@ModuleID int,
@ModuleTitle nvarchar
AS
UPDATE HtmlText
SET myTitle = @ModuleTitle
WHERE ModuleID = @ModuleID
GO
pretty straightforward there,
now what I want is something to combine the 2 like this so I only have to pass the ModuleID (I'll then get the title from the sproc getModuleTitle)
create proc myUpdate
@ModuleID int
exec getModuleTitle @ModuleID-- to return the ModuleTitle then use that in the update statement
UPDATE HtmlText
SET myTitle = @ModuleTitle
WHERE ModuleID = @ModuleID
GO
Can anybody shed some light on how this should look?
I think I need some OUTPUT somewhere but not sure where or how?
TIA
Sal
-
Try this
quote:
1.
CREATE PROCEDURE dbo.getModuleTitle
@ModuleID int OUTPUT
AS
blah...blah...
quote:
create proc myUpdate@ModuleID int
exec getModuleTitle @ModuleID OUTPUT
blah...blah...
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Thanks Phill,
Tried this exactly as you outlined but still no joy -- still get a TSQL syntax error
This is the first time I have tried something like this - perhaps there is better way to accomplish the same task?
Basically need to return a value (nvarchar 50) from passing in an ID then use that value in an udpate...
TIA
Sal
-
Here's how to use output parameters:
CREATE PROCEDURE dbo.getModuleTitle
@ModuleID int,
@ModuleTitle nvarchar(50) OUTPUT
AS
SET NOCOUNT ON
SELECT @ModuleTitle = ModuleTitle
FROM dbo.Modules
WHERE ModuleID = @ModuleID
go
CREATE PROC myUpdate
@ModuleID int
AS
SET NOCOUNT ON
DECLARE @ModuleTitle nvarchar(50)
EXEC getModuleTitle @ModuleID, @ModuleTitle OUTPUT
UPDATE dbo.HtmlText
SET myTitle = @ModuleTitle
WHERE ModuleID = @ModuleIDIf this is your actual problem, then it's easier to just use one update instead of the two SPs:
ALTER PROC myUpdate
@ModuleID int
AS
SET NOCOUNT ON
UPDATE t
SET myTitle = m.ModuleTitle
FROM dbo.HtmlText t JOIN dbo.Modules m ON t.ModuleID = m.ModuleIDYou were also missing the "AS" in your third SP. I added the SET NOCOUNT ON statements as it didn't seem relevant to your purposes to make the server calculate the rowcounts.
--Jonathan
--Jonathan -
Excellent thanks Jonathan!
Your suggestion too is just what I wanted!
-
It's really great thought. But If I want to execute a stored procedure which returns more than one value which I need to call subsequently in my nested procedure. How do I do. Then I tried using a temporary table to get the recordset of the first stored procedure and process that accordingly.
Way to go.
Cheers,
Ganesh
-
If you're using SQL 2000, use a table type variable.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
How would I get the following example of selecting fields from a stored procedure to work?
CREATE PROC usp_Test_SPExec
As
SELECT 1 As Field1, 2 As Field2, 3 As Field3
GO
SELECT Field1, Field3
INTO #Temp
FROM usp_Test_SPExec
Edited by - hamishahern on 10/09/2003 8:10:39 PM
-
Instead of creating a stored procedure, us a function.
If you have to use a stored procedure, try using OPENROWSET.
Your example would look like,
CREATE PROC usp_Test_SPExec
As
SELECT 1 As Field1, 2 As Field2, 3 As Field3
GO
SELECT Field1, Field3
INTO #Temp
FROM OPENROWSET('SQLOLEDB',',your server>';',<your login>';'<your password>',
'EXEC <your database>.dbo.usp_Test_SPExec') AS a
SELECT * FROM #TempHope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Edited by - phillcart on 10/09/2003 11:46:59 PM
--------------------
Colt 45 - the original point and click interface
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply