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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.