SQL Server: Conversion failed when converting from a character string to uniqueidentifier.

Question

I've been banging my head against a wall for the past hour trying to figure this out, sql is giving me the following error

Msg 8169, Level 16, State 2, Procedure GetAppointmentsByProfessionalName, Line 6
Conversion failed when converting from a character string to uniqueidentifier.

when this stored procedure is executed

    -- =============================================
    -- Create date: <July 2010>
    -- Description: <Gets a list of appointments for a professionals username>
    -- =============================================
    Drop procedure GetAppointmentsByProfessionalName
    go
    Create procedure GetAppointmentsByProfessionalName(@ProfessionalName varchar(256))
    as
     declare @ProfessionalID uniqueidentifier
     set @ProfessionalID = (select UserId from aspnet_Users where UserName = @ProfessionalName)

     select a.AppointmentID as 'Appointment ID', 
       c.Name as 'Client Name', 
       p.Name as 'Professional Name', 
       a.ProposedDate as 'Date', 
       CONVERT(CHAR(8), a.ProposedTime, 114)as  'Time', 
       a.ClientDescription as 'Client Notes', 
       a.Confirmed as 'Confirmed Appointment'
     from Appointment a join Client c on a.ForClientID = c.ClientID
          join dbo.Professional p on a.ForProfessionalID = p.ProfessionalID
     where ForProfessionalID = @ProfessionalName
    go

I am using the default asp.net membership tables, as well as the following definitions

create table Professional
(
 ProfessionalID uniqueidentifier not null constraint pk_ProID Primary Key references aspnet_Users(UserId),
 Name varchar(256),
 Email varchar(256),
 Phone varchar(256),
 DisplayPictureUrl varchar(256),
 ProfileSubHeader varchar(1000),
 ProfileContent varchar(1000),
 ServicesSubHeader varchar(1000),
 ServicesContent varchar(1000)
)

go

create table Client
(
 ClientID int identity not null constraint pk_ClientID Primary Key clustered,
 Name varchar(256),
 Email varchar(256),
 Phone varchar(256)
)

go

create table AppointmentType
(
 TypeID int identity not null constraint pk_AppointmentTypeID Primary Key clustered,
 Name varchar(256),
 Description varchar(256),
 DisplayPictureUrl varchar(256)
)

go

create table Appointment
(
 AppointmentID int identity not null constraint pk_AppointmentID Primary Key clustered,
 ForClientID int null constraint fk_ForClientID references Client(ClientID),
 ForProfessionalID uniqueidentifier not null constraint fk_ForProfessionalID references aspnet_users(UserID),
 ProposedTime datetime not null,
 ProposedDate datetime not null,
 TypeID int not null constraint fk_TypeID references AppointmentType(TypeID),
 ClientDescription varchar(256) null,
 Confirmed bit
)

GO

Its probably a syntax issue, I'm not too great with SQL yet. I'm hoping someone here will be able to spot my issue.

Answer 1

Your stored procedure accepts a varchar(256), but your WHERE statement is joining onto ForProfessionalID, which is a unique identifier.

Answer 2

the problem is:

where ForProfessionalID = @ProfessionalName

I think you want

where ForProfessionalID = @ProfessionalID