tsql - first attempt at a sproc transaction -


i trying write sproc transaction. can tell me if there issues code below, or if work intended?

alter procedure [dbo].[deletemetricmeter]  (     @sectionid int,     @metricmeterid int,     @result bit output )   declare @metricmetercount int declare @err int declare @rowcount int  set xact_abort on  begin tran  select @metricmetercount = count(*) lumetricmeters fksectionid = @sectionid  select @err = @@error, @rowcount = @@rowcount   if (@err <> 0) or (@rowcount = 0) begin     goto on_error            end  delete lumetricmeterlist pkmetricmeterid = @metricmeterid  select @err = @@error, @rowcount = @@rowcount   if (@err <> 0) or (@rowcount = 0) begin     goto on_error            end   delete lumetricmeters pkmetricmeterid = @metricmeterid  select @err = @@error, @rowcount = @@rowcount   if (@err <> 0) or (@rowcount = 0) begin     goto on_error            end  if (@metricmetercount = 1) begin     delete lumetricsections     pksectionid = @sectionid      select @err = @@error, @rowcount = @@rowcount       if (@err <> 0) or (@rowcount = 0)     begin         goto on_error                end end  commit tran set @result = 1 return @result  on_error:     rollback tran     set @result = 0     return @result

there few issues procedure:

  • you should not evaluate rowcount unless indicates error. when there no data deleted there no need rollback.

  • your code not thread safe. metricmetercount query should changed prevent other threads performing delete in between select & execution of delete:

    select @metricmetercount = count(*)    lumetricmeters (xlock, serializable)  fksectionid = @sectionid

i write code this:

alter procedure [dbo].[deletemetricmeter]  (     @sectionid int,     @metricmeterid int,     @result bit output )   declare @metricmetercount int declare @err int declare @rowcount int  set xact_abort on  begin tran  delete lumetricmeterlist   pkmetricmeterid = @metricmeterid  select @err = @@error  if (@err <> 0)      goto on_error             delete lumetricmeters  pkmetricmeterid = @metricmeterid  select @err = @@error  if (@err <> 0)      goto on_error             if exists (select *               lumetricmeters (xlock, serializable)              fksectionid = @sectionid) begin     delete lumetricsections       pksectionid = @sectionid      select @err = @@error     if (@err <> 0)          goto on_error            end  commit tran  return (0)  on_error:     rollback tran     return (-1) go

note: return values should 0 success , negative number failure.


Comments

Post a Comment

Popular posts from this blog

apache - Add omitted ? to URLs -

i working on website cms reads page name of query string, e.g. http://exmaple.com/?pagename . trying create rewrite rule rewrite ? . so, user types in /pagename , taken /?pagename try this: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^/?(.*) index.php?$1 [l]
Read more

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

i using bbpress installation without wordpress integration. the redirect of rewriteengine @ forum site works: rewriteengine on rewritecond %{http_host} ^forum.mysite.com$ rewriterule (.*)$ http://www.forum.mysite.com/$1 [r=301,l] the problem login pppress. bbpress login not recognize: http://www.forum.mysite/bb-login.php . it redirects to: http://forum.mysite.com/bb-login.php . i tried permalink settings none , name based, same issue. does uses redirect in bbpress or how can eliminate "double" content , redirect permanently www.? i redirected www.forum.mysite.com forum.mysite.com , achieved eliminate "double" content.
Read more

php - How can I stop spam on my custom forum/blog? -

so have custom built forum & blog system of late has been dealing lot of spam. if wordpress use akismet, if different common platform i'm sure i'd find plugin. there kind of static class can download this? using php. i'd still go akismet, if it. uses outside of wordpress, may have pay fee it, depending on use -- check terms , conditions -- it's option , easy implement in php using api. use api key wordpress. com account access. basically, grab whichever php client library takes fancy -- use alex potsides' php5 library -- plug in key, , it's handful of lines of code. here's bare bones of validation straight 1 of live sites: ... if ($akismet) { $akismet->setcommentauthor($name); $akismet->setcommentauthoremail($session->userinfo["email"]); $akismet->setcommentauthorurl(""); $akismet->setcommentcontent($sentence);...
Read more