Link to home
Start Free TrialLog in
Avatar of stretch73
stretch73Flag for United States of America

asked on

SQL Server 2005 Cast Question

I am working on the following stored procedure:

ALTER PROCEDURE [dbo].[app_BMProjects_Select_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?
Avatar of strickdd
strickdd
Flag of United States of America image

Try this:

AND (TPOWebID = CAST(@projNum AS varchar(100)) OR AIT = CAST(@projNum AS varchar(100)) OR ECMSID = CAST(@projNum AS varchar(100)) OR g_PCMID =
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?
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.
Avatar of twoboats
twoboats

or, check they're numeric before casting them with isnumeric...
Avatar of stretch73

ASKER

Strick,

Added your change, now I'm getting this error:

Conversion failed when converting the varchar value 'P10821' to data type int.
Forgot to mention 'P10821' is the g_PCMID being passed in.
ASKER CERTIFIED SOLUTION
Avatar of strickdd
strickdd
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Guy Hengel [angelIII / a3]
>                  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

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.
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?
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.


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)