Calling a stored procedure in another 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

General SQL Server Forums

New to SQL Server Programming

Calling a stored procedure in another stored proc

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 16:13:51

Is this works when we call a stored procedure in another stored procedure

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 16:14:27

Yes it works fine.

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

Subscribe to my blog

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 17:32:43

I need to call a procedure which has two parameters in another procedure and store the result in a variable

Please help me out!!

Thanks,
reacha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 17:45:14

Does it have an output variable?

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

Subscribe to my blog

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 18:02:38

yes it has an output variable

exec [msp_Userid&AuditStampCompleted] @pkgid, @result = @result output

but here i am not getting correct values

everything is coming as zeros

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 18:11:23

Run it in Management Studio to see if you are getting the correct result.

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

Subscribe to my blog

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 18:14:44

i tried running but getting all zeros for each and every packageID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 18:16:27

Then you'll need to debug the stored procedure. If you want us to help, then you'll need to post the code.

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

Subscribe to my blog

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 18:25:11

This is the inner procedure

ALTER procedure [msp_Userid&AuditStampCompleted](@pkgid int,@result varchar(30) output)
as

set @result = (select top(1) (CONVERT(varchar(30), userid, 113))+''+ '@'+''+ convert(nvarchar(30)AUDIT.Auditstamp,113) from audit
where AUDIT.Audittype =0
and AUDIT.PKGID = @pkgid
order by AUDIT.Auditstamp desc)

Main procedure
--------------

declare @pkgid int

declare x
cursor for select pkgid from AUDIT_Metrics
where AuditStampCompleted is null

open x

Declare @AuditStampLocked datetime;
Declare @result varchar(30);

fetch next from x into @pkgid

WHILE @@Fetch_Status = 0

BEGIN

exec [msp_Userid&AuditStampCompleted] @pkgid, @result = @result output

set @AuditStampLocked = (SELECT [dbo].[mfun_AuditStampLocked] (@pkgid))

UPDATE AUDIT_Metrics
SET USERID = @userid,QueueEventID = @QueueEventID,AuditStampLocked=@AuditStampLocked,AuditStampCompleted = @AuditStampCompleted
WHERE PKGID = @pkgid

print @pkgid
fetch next from x into @pkgid

END

close x;
deallocate x;

Thanks,
reacha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 18:27:57

So this needs to be figured out:

set @result = (select top(1) (CONVERT(varchar(30), userid, 113))+''+ '@'+''+ convert(nvarchar(30)AUDIT.Auditstamp,113) from audit
where AUDIT.Audittype =0
and AUDIT.PKGID = @pkgid
order by AUDIT.Auditstamp desc)

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

Subscribe to my blog

reacha
Starting Member

49 Posts

Posted - 2010-10-08 : 18:36:35

No here the output is correct but in the main procedure

i was not getting the @result value correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-08 : 18:54:48

I don't see anything wrong in your code. I suppose you need to figure out if @pkgid is being set properly by the cursor.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources