troubleshooting Question

SQL Server 2005 Cast Question

Avatar of stretch73
stretch73Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
13 Comments1 Solution272 ViewsLast Modified:
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?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros