stretch73
asked on
SQL Server 2005 Cast Question
I am working on the following stored procedure:
ALTER PROCEDURE [dbo].[app_BMProjects_Sele ct_nw]
(
@FourDot VARCHAR(10),
@searchName VARCHAR(100),
@projNum VARCHAR,
@projectTypeCode VARCHAR(10)
)
AS
--SET NOCOUNT ON
IF @FourDot = 'All'
BEGIN
SELECT
ProjectID,
FourDot,
ProjectTypeCode,
ProjectName,
CASE WHEN ProjectTypeCode = 'B' THEN AIT ELSE TPOWebID END AS Num
FROM app_Projects
WHERE
(projectName LIKE '%' + @searchName + '%')
AND (TPOWebID = CAST(@projNum AS INT) OR AIT = CAST(@projNum AS INT) OR ECMSID = CAST(@projNum AS INT) OR g_PCMID = @projNum OR ISNULL(@projNum,'') = '')
AND ProjectTypeCode = (CASE WHEN @projectTypeCode = 'All' THEN ProjectTypeCode ELSE @projectTypeCode END)
END
I'm trying to allow the user to search on AIT, ECMSID, or g_PCMID from the same textbox. AIT and ECMSID are both integers but g_PCMID is a VARCHAR and has an alpha character in it. I keep getting a 'Conversion failed when converting the varchar value 'P' to data type int.' error and I'm not sure I'm using the CAST statement correctly.
Any thoughts?
ALTER PROCEDURE [dbo].[app_BMProjects_Sele
(
@FourDot VARCHAR(10),
@searchName VARCHAR(100),
@projNum VARCHAR,
@projectTypeCode VARCHAR(10)
)
AS
--SET NOCOUNT ON
IF @FourDot = 'All'
BEGIN
SELECT
ProjectID,
FourDot,
ProjectTypeCode,
ProjectName,
CASE WHEN ProjectTypeCode = 'B' THEN AIT ELSE TPOWebID END AS Num
FROM app_Projects
WHERE
(projectName LIKE '%' + @searchName + '%')
AND (TPOWebID = CAST(@projNum AS INT) OR AIT = CAST(@projNum AS INT) OR ECMSID = CAST(@projNum AS INT) OR g_PCMID = @projNum OR ISNULL(@projNum,'') = '')
AND ProjectTypeCode = (CASE WHEN @projectTypeCode = 'All' THEN ProjectTypeCode ELSE @projectTypeCode END)
END
I'm trying to allow the user to search on AIT, ECMSID, or g_PCMID from the same textbox. AIT and ECMSID are both integers but g_PCMID is a VARCHAR and has an alpha character in it. I keep getting a 'Conversion failed when converting the varchar value 'P' to data type int.' error and I'm not sure I'm using the CAST statement correctly.
Any thoughts?
The issue is that you allow @projNum as a varchar (and you should always add a size to a varchar data type, the default may not be correct).
So you have TPOWebID but you are relying on the fact that you can cast the @projNum as an integer. Why?
So you have TPOWebID but you are relying on the fact that you can cast the @projNum as an integer. Why?
Yes, cast everything as varchar data types instead of int.
You're going to need to cast your int fields as some type of character type (would suggest varchar) to compare them, not the other way around.
An int can be converted to a varchar, but the other way doesn't work.
An int can be converted to a varchar, but the other way doesn't work.
or, check they're numeric before casting them with isnumeric...
ASKER
Strick,
Added your change, now I'm getting this error:
Conversion failed when converting the varchar value 'P10821' to data type int.
Added your change, now I'm getting this error:
Conversion failed when converting the varchar value 'P10821' to data type int.
ASKER
Forgot to mention 'P10821' is the g_PCMID being passed in.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> CASE WHEN ProjectTypeCode = 'B' THEN AIT ELSE TPOWebID END AS Num
might be the problem. are both AIT and TPOWebID of the same data type?
if not:
CASE WHEN ProjectTypeCode = 'B' THEN CAST(AIT AS VARCHAR(10)) ELSE CAST(TPOWebID AS VARCHAR(10)) END AS Num
>Forgot to mention 'P10821' is the g_PCMID being passed in.
>OR ECMSID = CAST(@projNum AS INT)
so, when @projNum is passed as 'P10821', you cannot cast to int that value.
you will have to make it the other way round:
CAST( ECMSID AS VARCHAR(10)) = @projNum
might be the problem. are both AIT and TPOWebID of the same data type?
if not:
CASE WHEN ProjectTypeCode = 'B' THEN CAST(AIT AS VARCHAR(10)) ELSE CAST(TPOWebID AS VARCHAR(10)) END AS Num
>Forgot to mention 'P10821' is the g_PCMID being passed in.
>OR ECMSID = CAST(@projNum AS INT)
so, when @projNum is passed as 'P10821', you cannot cast to int that value.
you will have to make it the other way round:
CAST( ECMSID AS VARCHAR(10)) = @projNum
ASKER
I tried the casting on the other side:
AND (CAST(TPOWebID AS VARCHAR(20)) = @projNum
OR CAST(AIT AS VARCHAR(20)) = @projNum
OR CAST(ECMSID AS VARCHAR(20)) = @projNum
OR g_PCMID = @projNum
OR ISNULL(@projNum,'') = '')
and the query compiles but it's not bringing the record back.
AND (CAST(TPOWebID AS VARCHAR(20)) = @projNum
OR CAST(AIT AS VARCHAR(20)) = @projNum
OR CAST(ECMSID AS VARCHAR(20)) = @projNum
OR g_PCMID = @projNum
OR ISNULL(@projNum,'') = '')
and the query compiles but it's not bringing the record back.
Look at the data.
Do you have a TPOWebID that equals the @projNum?
Do you have a AIT that equals the @projNum?
Do you have a ECMSD that equals the @projNum?
Do you have a g_PCMID that equals the @projNum?
Because you are using @projNum as a parameter, why check if it is NULL at the last step?
Do you have a TPOWebID that equals the @projNum?
Do you have a AIT that equals the @projNum?
Do you have a ECMSD that equals the @projNum?
Do you have a g_PCMID that equals the @projNum?
Because you are using @projNum as a parameter, why check if it is NULL at the last step?
ASKER
Yes, there is a record where the g_PCMID equals the @projNum.
We check if it is NULL on the last step because sometimes that parameter is not provided. This query is getting called from a search form where the user can provide fourDot, searchName, (TPOWebID, AIT, ECMSID, or g_PCMID), and project type code. Thus, if they provide any of the parameters except one of the IDs the query will ignore it.
We check if it is NULL on the last step because sometimes that parameter is not provided. This query is getting called from a search form where the user can provide fourDot, searchName, (TPOWebID, AIT, ECMSID, or g_PCMID), and project type code. Thus, if they provide any of the parameters except one of the IDs the query will ignore it.
Is g_PCMID the same size and data type as the varchar @projNum? I ask because that is the only value that you have not CAST.
Does the query return an expected result if you eliminate the other parameters?
DECLARE @projNum varchar(6)
DECLARE @searchname varchar(100)
DECLARE @projecttypecode varchar(10)
SET @projNum = 'P10821'
SET @searchname = (some value that equals projectname)
SET @projecttypecode = (whatever value)
SELECT
ProjectID,
FourDot,
ProjectTypeCode,
ProjectName,
CASE WHEN ProjectTypeCode = 'B' THEN AIT ELSE TPOWebID END AS Num
FROM app_Projects
WHERE
(projectName LIKE '%' + @searchName + '%')
AND g_PCMID = @projNum OR ISNULL(@projNum,'') = '')
AND ProjectTypeCode = (CASE WHEN @projectTypeCode = 'All' THEN ProjectTypeCode ELSE @projectTypeCode END)
AND (TPOWebID = CAST(@projNum AS varchar(100)) OR AIT = CAST(@projNum AS varchar(100)) OR ECMSID = CAST(@projNum AS varchar(100)) OR g_PCMID =