Posted By

wedgardigitalwkscom on 06/28/11


Tagged

insert create


Versions (?)

Create New User


 / Published in: SQL
 

URL: http://www.digitalwks.com/blogs/wilson-edgar/2011/06/27/stored-procedures-multiples-resulset-and-table-value-parameters

  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5.  
  6. CREATE PROCEDURE TestApp_UserCreate
  7. @Name NVARCHAR(128),
  8. @DateOfBirth DATE,
  9. @IdentityCard NVARCHAR(64) = NULL,
  10. @Nickname NVARCHAR(128) = NULL,
  11. @Email NVARCHAR(128),
  12. @Gender BIT,
  13. @MaritalStatusID SMALLINT = NULL,
  14. @NumberOfChildren SMALLINT = NULL,
  15. @UserID INT OUTPUT
  16. AS
  17. BEGIN
  18. BEGIN TRANSACTION
  19. INSERT INTO Users(Name, DateOfBirth, IdentityCard, Nickname, Email, Gender, MaritalStatusID, NumberOfChildren, CreateDate)
  20. VALUES(@Name, @DateOfBirth, @IdentityCard, @Nickname, @Email, @Gender, @MaritalStatusID, @NumberOfChildren, GETDATE())
  21. IF(@@ERROR <> 0)
  22. BEGIN
  23. ROLLBACK TRANSACTION
  24.  
  25. END
  26. ELSE
  27. BEGIN
  28. SELECT TOP 1 @UserID = UserID FROM Users ORDER BY CreateDate DESC
  29. COMMIT TRANSACTION
  30. END
  31. END
  32. GO

Report this snippet  

You need to login to post a comment.