XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   Dear SQLers:   
      
        Having been off on something else, I come back to my slow   
   progress with JavaScript and SQL Server.   
      
        I have a stored procedure for retrieving a row given the key. The   
   results of this SP (the last table selected AIUI) will be returned to   
   a Web page.   
      
   ***** Start of Included Code *****   
   -- GetPriRow   
   -- Return the Primus Row Matching the Key   
   -- Last Modification: 2012-07-30   
      
   drop procedure GetPriRow   
   go   
   create procedure GetPriRow   
    (   
    @theKey varchar(4)   
    )   
   as   
      begin   
      select * from Primus where PKPri=@theKey   
      if (@@rowcount=0)   
         begin   
         create table #Error   
          (   
          OrderBy int not null,   
          ErrorResponse varchar(max)   
          )   
         insert into #Error   
          (OrderBy,ErrorResponse)   
         values   
          (0,'Key not found.')   
         select * from #Error   
         return   
         end   
      select * from Primus where PKPri=@theKey   
      end   
   go   
   ***** End of Included Code *****   
      
        This code has issues.   
      
     1) I expect to have to create and insert to #Error quite frequently.   
   I would like a stored procedure for each of these.  Unfortunately, due   
   to the lifetime rules for temporary tables, if I create #Error in its   
   own SP, it will be deleted as soon as the creation SP terminates.  The   
   above is more verbose than I like.  I would prefer to be able to code   
   something like the following in-line in my SPs:   
             execute CreErrTbl   
             execute InsErrRow 0,'Key not found.'   
   Is there a cleaner way to do this?   
      
     2) I query Primus twice, once for the row count and once for real. I   
   do wish to catch no rows as an error condition.  I wish to avoid race   
   conditions.  (I think I have a race condition here in the case of the   
   row being deleted just after the first time, but before the second.   
   Am I correct in thinking this?  If so, how do I correct it?)   
      
     3) The front-end will be a Web page.  I want to retrieve a row, edit   
   it, and write it back.  I would like that any changes in the meantime   
   are not overwritten.  As far as I can see, this is not going to work   
   to protect meantime changes, and I need to do something more.   
      
        Can I lock the read row?  If so:   
     a) Can I release the lock after updating the row?  This would be in   
   a separate stored procedure and a different XMLHttpRequest.   
     b) Can I have the row lock expire after a period of time (say, 15   
   minutes)?  Someone could close his browser after reading a row, and I   
   do not want the row lock kept indefinitely.   
      
     4) I would like to avoid using ActiveX to communicate between the   
   Web page and SQL Server and preferably without having to add more   
   software to the server.  Otherwise, my app is going to be limited to   
   IE.  I have been unable to find any details on how to do this.   
   Pointers would be appreciated.   
      
        For all of the above, what is the stuff called (so I can search   
   it down (though good links will be appreciated)), and what is the   
   syntax?   
      
        Are there any other details I should give?  Are there any other   
   questions that I should be asking?   
      
   Sincerely,   
      
   Gene Wirchenko   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)