XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   Gene Wirchenko (genew@ocis.net) writes:   
   >   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?   
      
   Why wouid you creaet the temp table at all? Why not just return a result set   
      
      SELECT 0 AS OrderBy, 'Key not found.' AS ErrorReponse   
      
   Also, most clients gets confused if a stored procedure returns two results   
   sets with different shape. Of course, it's perfectly manageable if you   
   program the client correctly, but it's a little dubious.   
      
   >   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?)   
      
   So the purpose with the above procedure is to check for existence? Why in   
   that case not use   
      
      SELECT @output = CASE WHEN EXISTS (select *   
                                         from Primus where PKPri=@theKey)   
                            THEN 1   
                            ELSE 0   
                      END   
      
   And this scraps the result set above entirely. The stored procedure have no   
   reason to ponder whether existence is an error not. The job of the procedure   
   is return whether there is a row or not. It's up to the caller to consider   
   that to be an error or not.   
      
   As for your actual question, the answer is that if you are in a transaction   
   and you add the table hint (SERIALISABLE) you will prevent a row to be   
   inserted until you commit. However, see the next point:   
      
      
   >   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.   
      
   Yes, you can lock a row if you read it in a transction and the isolation   
   level is at least PEPEATABLE READ. The row will be released when you commit.   
      
   BUT DON'T DO THIS! Having a transaction in progress when you are waiting for   
   user input is an absolute no-no.   
      
   The most commonly used solution is optimisitic concurrency. You add a   
   timestamp (a.k.a rowversion) column to the table. This column is auto-   
   matically updated with a database-unique monotonically growing value when a   
   row is updated. Thus, you read the timestamp value and when you write back   
   you compare, and if they are different someone else has come in between.   
      
   If you want pessimistic concunrrency, you will have to roll your own.   
      
   >   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.   
      
   Sounds like a question for a different newsgroup.   
      
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)