AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Wednesday 14 October 2015

Insert & update records by single stored procedure

In this blog post we are going to discuss Insert & update records by single stored procedure. So let's discuss about this method.

DataBase Table Name : UserDetail
Column Name   Data Type 
 UserId  int(primary key)
 UserName  nvarchar(max)
 FirstName  nvarchar(max)
 LastName  nvarchar(max)
 Email  nvarchar(max)
Create new Stored Procedure by name sp_InsertUpdateUser
-- =============================================
-- Author:      Shivam Srivastava
-- Description: Insert Update User
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertUpdateUser]
    @UserId INT = 0 ,
    @UserName NVARCHAR(MAX) ,
    @FirstName NVARCHAR(MAX) ,
    @LastName NVARCHAR(MAX) ,
    @Email NVARCHAR(MAX) ,
    @Output INT = 0 OUT
AS 
    BEGIN   
        SET NOCOUNT ON ;      
        IF NOT EXISTS ( SELECT TOP 1
                                UserId
                        FROM    dbo.UsersDetail
                        WHERE   UserID = @UserID ) 
            BEGIN
                INSERT  INTO [dbo].[UsersDetail]
                        ( [UserName] ,
                          [FirstName] ,
                          [LastName] ,
                          [Email]                         
                        )
                VALUES  ( @UserName ,
                          @FirstName ,
                          @LastName ,
                          @Email 
                        )
                SET @Output = SCOPE_IDENTITY()  
            END
        ELSE 
            BEGIN 
                UPDATE  [dbo].[UsersDetail]
                SET     [UserName] = @UserName ,
                        [FirstName] = @FirstName ,
                        [LastName] = @LastName ,
                        [Email] = @Email
                WHERE   UserId = @UserId
                SET @Output = @userId
            END
   
    END
Stored Procedure will return UserId as @Output parameter.

0 comments :

Post a Comment

  • Popular Posts
  • Comments