Solved

Call stored procedure from another stored procedure

Posted on 2012-03-15
14
412 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 37724700
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
ID: 37725027
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:Scott Pletcher
ID: 37725335
>> 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:Leo Torres
ID: 37725504
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
ID: 37726544
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:Scott Pletcher
ID: 37726740
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:Scott Pletcher
ID: 37726783
...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
 
LVL 1

Author Comment

by:webressurs
ID: 37728624
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:Scott Pletcher
ID: 37729378
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
ID: 37729421
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:
Scott Pletcher earned 500 total points
ID: 37729484
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
ID: 37729627
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:Scott Pletcher
ID: 37729756
No, yours will loop endlessly re-processing the first row.
0
 
LVL 1

Author Closing Comment

by:webressurs
ID: 37732321
Thank you, this works perfect!!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 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