This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

What are the best practices in writing a sql stored procedure

By: a guest on Feb 28th, 2012  |  syntax: None  |  size: 1.46 KB  |  views: 27  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. EXEC @err = AnyStoredProc @value
  2. SET  @save_error = @@error
  3. -- NULLIF says that if @err is 0, this is the same as null
  4. -- COALESCE returns the first non-null value in its arguments
  5. SELECT @err = COALESCE( NULLIF(@err, 0), @save_error )
  6. IF @err <> 0 BEGIN
  7.     -- Because stored proc may have started a tran it didn't commit
  8.     ROLLBACK TRANSACTION
  9.     RETURN @err
  10. END
  11.        
  12. INSERT, DELETE, UPDATE
  13. SELECT INTO
  14. Invocation of stored procedures
  15. invocation of dynamic SQL
  16. COMMIT TRANSACTION
  17. DECLARE and OPEN CURSOR
  18. FETCH from cursor
  19. WRITETEXT and UPDATETEXT
  20.        
  21. /*
  22.     Usage:
  23.     EXEC usp_ThisProc @Param1 = 1, @Param2 = 2
  24. */
  25.        
  26. SET NOCOUNT ON
  27. BEGIN TRAN
  28.   INSERT...
  29.   UPDATE...
  30. COMMIT
  31.        
  32. SET NOCOUNT ON
  33. BEGIN TRAN
  34.   INSERT...
  35.   UPDATE...
  36. COMMIT
  37.        
  38. SET NOCOUNT ON
  39. BEGIN TRAN
  40.   INSERT...
  41.   IF @ErrorVar <> 0
  42.   BEGIN
  43.       RAISERROR(N'Message', 16, 1)
  44.       GOTO QuitWithRollback
  45.   END
  46.  
  47.   UPDATE...
  48.   IF @ErrorVar <> 0
  49.   BEGIN
  50.       RAISERROR(N'Message', 16, 1)
  51.       GOTO QuitWithRollback
  52.   END
  53.  
  54.   EXECUTE @ReturnCode = some_proc @some_param = 123
  55.   IF (@@ERROR <> 0 OR @ReturnCode <> 0)
  56.        GOTO QuitWithRollback
  57. COMMIT
  58. GOTO   EndSave              
  59. QuitWithRollback:
  60.     IF (@@TRANCOUNT > 0)
  61.         ROLLBACK TRANSACTION
  62. EndSave:
  63.        
  64. SET NOCOUNT ON
  65. SET XACT_ABORT ON
  66. BEGIN TRY
  67.     BEGIN TRAN
  68.     INSERT...
  69.     UPDATE...
  70.     COMMIT
  71. END TRY
  72. BEGIN CATCH
  73.     IF (XACT_STATE()) <> 0
  74.         ROLLBACK
  75. END CATCH
  76.        
  77. SET NOCOUNT ON
  78. SET XACT_ABORT ON
  79. BEGIN TRAN
  80.     INSERT...
  81.     UPDATE...
  82. COMMIT
clone this paste RAW Paste Data