Advertisement
Guest User

What are the best practices in writing a sql stored procedure

a guest
Feb 28th, 2012
44
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.46 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement