USE [test]
GO
/****** Object: StoredProcedure [dbo].[AddUpdateEmpData_sp] Script Date: 10/17/2015 14:21:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec dbo.AddUpdateEmpData_sp '','Mr','ABC','PQR','XYZ','APX','ENGG','M','','abc@gmail.com','xyz@gmail.com','2302046','253','2485918','240','1234','98437424922','12345','',''
ALTER PROCEDURE [dbo].[AddUpdateEmpData_sp]( @UserID uniqueidentifier,
@Title nvarchar(100),
@FirstName nvarchar(100),
@MiddleName nvarchar(100),
@LastName nvarchar(200),
@DisplayName nvarchar(max),
@Salutation nvarchar(5),
@Gender nvarchar(1),
@ManagerID uniqueidentifier,
@Email nvarchar(150),
@AlternateEmail nvarchar(300),
@Phone nvarchar(20),
@PhoneExt nvarchar(5),
@Phone1 nvarchar(20),
@Phone1Ext nvarchar(5),
@Fax nvarchar(20),
@Mobile nvarchar(20),
@Pager nvarchar(20),
@CreatedBy uniqueidentifier,
@ModifiedBy uniqueidentifier
)
AS
BEGIN
declare @ActiveStatus bit,@CreatedOn datetime,@ModifiedOn datetime
--insert
set @ActiveStatus='1'
set @CreatedOn= GETDATE()
set @ModifiedOn = GETDATE()
if(@UserID is null)
BEGIN
set @UserID=NEWID()
set @CreatedBy=@UserID
set @ModifiedBy=@UserID
insert into test.dbo.EmpDb(UserID,Title,FirstName,MiddleName,LastName,DisplayName,Salutation,Gender,ManagerID,
Email,AlternateEmail,Phone,PhoneExt,Phone1,Phone1Ext,Fax,Mobile,Pager,ActiveStatus,
CreatedOn,CreatedBy,ModifiedOn,ModifiedBy)
VALUES(@UserID,@Title,@FirstName,@MiddleName,@LastName,@DisplayName,@Salutation,@Gender,@ManagerID,
@Email,@AlternateEmail,@Phone,@PhoneExt,@Phone1,@Phone1Ext,@Fax,@Mobile,@Pager,@ActiveStatus,
@CreatedOn,@CreatedBy,@ModifiedOn,@ModifiedBy);
if(@@ROWCOUNT=0)
begin
--error
select 'Record Not Inserted' as ReturnStatus---Record Not Saved
return
end
select 'Record Inserted SuccessFully' as ReturnStatus--- Record Saved Successfully
END
else
begin
--update
update test.dbo.EmpDb set Title=@Title,FirstName=@FirstName,MiddleName=@MiddleName,LastName=@LastName,DisplayName=@DisplayName,
Salutation=@Salutation,Gender=@Gender,ManagerID=@ManagerID,Email=@Email,AlternateEmail=@AlternateEmail,Phone=@Phone,
PhoneExt=@PhoneExt,Phone1=@Phone1,Phone1Ext=@Phone1Ext,Fax=@Fax,Mobile=@Mobile,Pager=@Pager,ActiveStatus=@ActiveStatus,
ModifiedBy=@ModifiedBy
WHERE UserID=@UserID;
if(@@ROWCOUNT=0)
begin
--error
select 'Record Not Updated' as RetStatus---Record Not Saved
return
end
select 'Record Updated Successfully' as RetStatus--- Record Saved Successfully
end
END
this is my stored procedure, the problem i facing is
Msg 8114, Level 16, State 5, Procedure AddUpdateEmpData_sp, Line 0
Error converting data type varchar to uniqueidentifier.