Solved

Call stored procedure from another stored procedure

Posted on 2012-03-15
14
408 Views
Last Modified: 2012-03-17
I have a custom blog system containing 3 tables; tblBlog, tblBlogEntry and tblBlogEntryComment:


--------------------
tblBlog
 --------------------
  id
  siteId
  Name

--------------------
tblBlogEntry
--------------------
  Id
  BlogId
  Text
  LastComment

--------------------
tblBlogEntryComment
--------------------
  Id
  EntryId
  UserName
  Text  


When a blog is deleted I have a stored procedure (spDeleteBlog) that cleans the database. This is how the "comments" are removed:


ALTER PROCEDURE [dbo].[spDeleteBlog]
(
  @UserName varchar(256),
  @SiteId smallint
)
AS
BEGIN
  SET NOCOUNT ON

  DECLARE @BlogId int;
  SET @BlogId = (SELECT Id FROM tblBlog WHERE UserName = @Username AND SiteId = @SiteId);

  DELETE C FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

  -- DELETE OTHER BLOG CONTENT HERE....

END


When deleting "comments" from tblBlogEntryComment I need to update the "Last Comment" in tblBlogEntry. I have a stored procedure (spDeleteBlogEntryComment) that delete comments correct and update tblBlogEntry.LastComment at the same time:


ALTER PROCEDURE [dbo].[spDeleteBlogEntryComment]
(
      @CommentId int,
      @SiteId smallint
)
AS
BEGIN
    SET NOCOUNT ON
      BEGIN
        -- DELETES COMMENTS AND UPDATES LASTCOMMENT CORRECT HERE!
      END
END


The question is; how can this DELETE statement be modifyed:

  DELETE C FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

... by calling this stored procedure instead:

EXEC spDeleteBlogEntryComment @CommentId, @SiteId;
0
Comment
Question by:webressurs
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
Sorry, don't understand what is the question.

If you want to call sp you should use EXEC.

What do you mean by modify DELETE statement?
0
 
LVL 1

Author Comment

by:webressurs
Comment Utility
Hi!

I try to explain another way... Let's say that this SQL deletes 10 comments:

DELETE C FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

Instead of using the SQL above I want to call the stored procedure "spDeleteBlogEntryComment" for each of these 10 comments:

EXEC spDeleteBlogEntryComment @CommentId, @SiteId;

Since spDeleteBlogEntryComment only delete 1 comment based on @CommentId and @SiteId I don't know how to delete all the 10 comments? Do I have to make some kind of loop?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> When deleting "comments" from tblBlogEntryComment I need to update the "Last Comment" in tblBlogEntry <<

But in this case, aren't you going to DELETE the corresponding tblBlogEntry anyway?
0
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
can you provide a screen shot in excel how tables look in a before and after state does not make sense to update something that is being deleted.. So I must assume you mean something else but we are not getting it.. You may need to specify that a delete in Y table updates X table value in columnX and the value to be put in that column
0
 
LVL 1

Author Comment

by:webressurs
Comment Utility
Screenshot

I try to explain the case first:

Jason make a comment in Karens blog. Then Karens blog is updated with LastComment = 03.03.2012 10:00:00 (example). If Jason deletes his profile and blog, all his comments is deleted.

Then Karens blog has to be updated because Jasons comment is deleted, and LastComment is no longer 03.03.2012 10:00:00. This is what the stored procedure spDeleteBlogEntryComment does, but only for one spesific commend based on CommenId.

So far, so good - I hope :)


This SQL deletes all comments for a given user (based on username and siteId):

DELETE C FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

But, the SQL above does NOT update LastComment as described before. That's why I want to change the SQL, and if possible, call the spDeleteBlogEntryComment.

Since spDeleteBlogEntryComment only updates one single comment at the time (based on CommentId and SiteId) I don't know how to call it.

In my dreamworld I would do something like this:

Exec spDeleteBlogEntryComment C.id, @SiteId where (SELECT C FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

Please see attached screenshot. Hope you understand the case now :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Yes.  Excellent explanation.

Unfortunately you almost have to use a cursor for that -- or rewrite the stored proc to accept a table/list of comment ids.

I would use an OUTPUT clause on the DELETE statement, then cursor through the OUTPUT results and EXEC the proc.  

Safest is to enclose the whole thing in a transaction so that it's all or none.  Otherwise you could DELETE all the comments, then fail during an EXEC, and some deleted comments would not get the proc called for them.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
...other_code_as_before...

DECLARE @commentIDs TABLE (
    commentID int
    )

DECLARE csrCommentIDs CURSOR FAST_FORWARD FOR
SELECT commentID
FROM @commentIDs

DECLARE @fetch_commentID int

BEGIN TRANSACTION;

DELETE C
FROM tblBlogEntryComment C
OUTPUT commentID INTO @commentIDs
INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id
INNER JOIN tblBlog ON tblBlogEntry.BlogId = tblBlog.Id
WHERE (C.UserName = @UserName) AND (tblBlog.SiteId = @SiteId);

OPEN csrCommentIDs;

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM csrCommentIDs INTO @fetch_commentID;
    IF @@FETCH_STATUS <> 0
        BREAK;
    EXEC spDeleteBlogEntryComment @CommentId, @SiteId;
END; --WHILE

DEALLOCATE csrCommentIDs;

COMMIT TRANSACTION;

...other_code_as_before...
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:webressurs
Comment Utility
Hi ScottPletcher!

Thank you so much. Now I think I understand this :)
Since spDeleteBlogEntryComment deletes each comment and updates LastCreated at the same time I have modified the code a bit. First I find all ID's by doing a SELECT, then I loop through the ID's and execute spDeleteBlogEntryComment. Hope this seems ok:


DECLARE @fetch_commentId int;
DECLARE  csrCommentIds CURSOR LOCAL FOR
SELECT C.Id FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id WHERE (tblBlogEntry.BlogId = @BlogId);

OPEN csrCommentIds;
FETCH NEXT FROM csrCommentIds INTO @fetch_commentId      WHILE @@FETCH_STATUS = 0
BEGIN
      EXEC spDeleteBlogEntryComment @fetch_commentId, @SiteId;
END
CLOSE csrCommentIds;
DEALLOCATE csrCommentIds;
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Looks good EXCEPT you need a FETCH after the EXEC.  Otherwise you'll just process the same row over and over in an endless loop.
0
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
try
DECLARE @fetch_commentId int;
DECLARE  csrCommentIds CURSOR LOCAL FOR
SELECT C.Id FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id WHERE (tblBlogEntry.BlogId = @BlogId);

OPEN csrCommentIds;
FETCH NEXT FROM csrCommentIds INTO @fetch_commentId      WHILE @@FETCH_STATUS = 0
BEGIN
      EXEC spDeleteBlogEntryComment @fetch_commentId, @SiteId;
END

FETCH NEXT FROM csrCommentIds INTO @fetch_commentId 

CLOSE csrCommentIds;
DEALLOCATE csrCommentIds;

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
Close ... the FETCH needs to be in the BEGIN ... END loop:


DECLARE @fetch_commentId int;
DECLARE  csrCommentIds CURSOR LOCAL FOR
SELECT C.Id FROM tblBlogEntryComment C INNER JOIN tblBlogEntry ON C.EntryId = tblBlogEntry.Id WHERE (tblBlogEntry.BlogId = @BlogId);

OPEN csrCommentIds;
FETCH NEXT FROM csrCommentIds INTO @fetch_commentId      
WHILE @@FETCH_STATUS = 0
BEGIN
      EXEC spDeleteBlogEntryComment @fetch_commentId, @SiteId;
      FETCH NEXT FROM csrCommentIds INTO @fetch_commentId
END

CLOSE csrCommentIds;
DEALLOCATE csrCommentIds;
0
 
LVL 8

Expert Comment

by:Leo Torres
Comment Utility
Suppose it could it has the same effect since ..

its executing 1 line of code ending then looping again..

I am not sure can you leave a Begin with out ending .. This will execute and get next without ending while..

Mines will complete the iteration thru the while loop then get the next value..


Hum now I wonder? webressurs can you try both and advise of any differences
Would be great to know
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
No, yours will loop endlessly re-processing the first row.
0
 
LVL 1

Author Closing Comment

by:webressurs
Comment Utility
Thank you, this works perfect!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now