CODEWITHSUNDEEP
sqlstored-procedures
tuckerjt07
tuckerjt07

Reputation: 922

SQL Stored Procedure Conversion Failed

I am having trouble with a stored procedure that is pulling information from the built in C# role and membership management tables to my own custom made tables. The procedure works fine up until the last Update statement.

ALTER PROCEDURE [dbo].[GetPerpsectiveMemberList]
AS
DECLARE @Members TABLE
(
RowNumber INT,
PerspectiveMemberID INT,
FirstName varchar(MAX),
LastName varchar(MAX),
[Address] varchar(MAX),
City varchar(MAX),
[State] varchar(MAX),
Zip varchar(MAX),
Email varchar(MAX),
ASPMemberID varchar(MAX),
ASPUsername varchar(MAX),
RoleID2 varchar(MAX),
RoleName2 varchar(MAX)
)
INSERT INTO @Members
SELECT ROW_NUMBER() OVER (ORDER BY Perspective_Member.PerspectiveMemberID), PerspectiveMemberID, Perspective_Member.FirstName, Perspective_Member.LastName, 
Perspective_Member.[Address], Perspective_Member.City, Perspective_Member.[State],   Perspective_Member.[State], Perspective_Member.Zip, Perspective_Member.ASPMemberID, 
Perspective_Member.ASPUsername,PerspectiveMemberID, ASPMemberID
FROM Perspective_Member
UPDATE @Members
SET RoleID2 = aspnet_UsersInRoles.RoleId
FROM aspnet_UsersInRoles
WHERE ASPMemberID = UserId
UPDATE @Members
SET RoleName2 = RoleName
FROM aspnet_Roles
WHERE RoleID2 = RoleID
SELECT * FROM @Members

When the procedure fails I get this message 

Conversion failed when converting from a character string to uniqueidentifier.

When I searched for this error message I found that everyone was having problems going from a string to the unique identifier but what is driving me crazy is that the only unique identifier being used in the last block is being used in the WHERE clause, and as I said earlier the procedure will run perfectly as long as the last UPDATE bl

Upvotes: 0

Views: 161

Answers (1)

BluesRockAddict
BluesRockAddict

Reputation: 15683

You have Perspective_Member.[State] listed twice:

Perspective_Member.[State],   Perspective_Member.[State]

Here is corrected INSERT statement:

INSERT INTO @Members
    SELECT ROW_NUMBER() OVER (ORDER BY Perspective_Member.PerspectiveMemberID), 
        PerspectiveMemberID, 
        Perspective_Member.FirstName, 
        Perspective_Member.LastName,
        Perspective_Member.[Address], 
        Perspective_Member.City,
        Perspective_Member.[State], 
        Perspective_Member.Zip,
        Perspective_Member.Email, 
        Perspective_Member.ASPMemberID,
        Perspective_Member.ASPUsername,
        PerspectiveMemberID, 
        ASPMemberID
    FROM Perspective_Member

Also, you should change the data type for your ASPMemberID and RoleID2 fields in @Members table to uniqueidentifier.

Update: it appears that the error is due to PerspectiveMemberID value (INT) being inserted into RoleID2 column (uniqueidentifier).

Upvotes: 3

Related Questions