While using 'CASE
' I encountered with problem that I'D never heard.
The problem is so: I'M using 'CASE
' statement in 'SELECT
' clause as a field. When calling columns with int data type, it's successful; However, When calling a varchar or datetime, it's unsuccessful.
?n the error result, it's said me to have marked the temporary field, including 'CASE
' statement, as int
, But not so. Please help...
The 'STORED PROCEDURE' Content
CREATE PROC spRegistry_PersonsGetField
@ID int,
@Field varchar(100)
AS
SELECT CASE @Field
WHEN 'Name' THEN Name
WHEN 'Surname' THEN Surname
WHEN 'Username' THEN Username
WHEN 'Password' THEN Password
WHEN 'Status' THEN dbo.fnStatus(Status)
WHEN 'EMail' THEN EMail
WHEN 'Tel' THEN dbo.fnIsEmpty(Tel)
WHEN 'Address' THEN dbo.fnIsEmpty(Address)
WHEN 'City' THEN dbo.fnIsEmpty(City)
WHEN 'BirthDate' THEN dbo.fnDate(BirthDate)
WHEN 'Gender' THEN dbo.fnGender(Gender)
WHEN 'Lang' THEN Lang
ELSE NULL
END
AS [Alan] varchar(200)
FROM Registry_Persons
WHERE ID = @ID
My Query
EXEC spRegistry_PersonsGetField 1, 'BirthDate'
The Error Message
See Question&Answers more detail:osConversion failed when converting the varchar value '04/11/2011' to data type int.