quote:

---

Originally posted by matdef

Hello, wondering if someone might be able to see where I am making an error.

I have a SPROC that updates muliple tables, is called from a trigger.. but seems to be a data type problem somewhere...
------------------
CREATE TRIGGER InsertMemberNumToUserID ON AR_MemberInfo
FOR INSERT
AS

EXEC AddUserCSI 'AR_MemberInfo.MemberNum','AR_MemberInfo.MbrFirstName','AR_MemberInfo.MbrLastName','AR_MemberInfo.Email'

----------------------
CREATE procedure dbo.AddUserCSI
@MemberNum Int,
@MbrFirstName nvarchar(50),
@MbrLastName nvarchar(50),
@MbrEmail nvarchar(100)

as
DECLARE @UserName Int
DECLARE @UserID Int
DECLARE @Password nvarchar(50)
SET @UserID = @MemberNum
SET @UserName = @MemberNum
SET @Password = SELECT AR_Financial.WebPassword FROM AR_financial WHERE AR_Financial.MemberNum = @MemberNum)
begin transaction
insert into WebUsers (
UserID,
FirstName,
LastName,
Email,
UserName,
Password
)
values (
@UserID,
@MbrFirstName,
@MbrLastName,
@MbrEmail,
@UserName,
@Password

if @@error <> 0
rollback transaction
else
insert into WebUserPortals (PortalID,UserID)
values (0,@UserID)
if @@error <> 0
rollback transaction
else
insert into WebUserRoles (UserID,RoleID)
values (@UserID,13)
if @@error <> 0
rollback transaction
else
insert into WebProfile (UserID,PortalID)
values (@UserID,0)
if @@error <> 0
rollback transaction
select SCOPE_IDENTITY()
GO
-------------



Then run query Analyzer:
-----------
USE MemberDB
GO

insert into AR_MemberInfo(MemberNum, MbrFirstName, MbrLastName, MbrEmail)
Values('999992', 'testfirst', 'testlast', 'matdef@md-interactive.com')
GO
--------------
and it errors with:
Server: Msg 8114, Level 16, State 4, Procedure AddUserCSI, Line 0
Error converting data type varchar to int.
The statement has been terminated.

MemberNum, UserID and UserName are all type "Int" in Table.
:(

---