Call stored procedure from another stored procedure

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;
LVL 1
webressursAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
patriktCommented:
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
 
webressursAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Scott PletcherSenior DBACommented:
>> 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
 
Leo TorresSQL DeveloperCommented:
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
 
webressursAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
...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
 
webressursAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Leo TorresSQL DeveloperCommented:
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
 
Leo TorresSQL DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
No, yours will loop endlessly re-processing the first row.
0
 
webressursAuthor Commented:
Thank you, this works perfect!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.