Advanced Search

Create New User

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.