troubleshooting Question

"Conversion Failed" error in SQL Server

Avatar of CyprexxDev
CyprexxDevFlag for United States of America asked on
Microsoft SQL Server 2005Microsoft AccessMicrosoft SQL Server
12 Comments1 Solution307 ViewsLast Modified:
I get this error regularly and have found it's by far the most annoying error in SQL Server.  What does it mean and why can't I do things like the following:

ALTER PROCEDURE [dbo].[procEntitySearch]
      @EntityTypeID int,
      @EntityName nvarchar(50) = NULL,
      @StreetNumber nvarchar(10) = NULL,
      @StreetName nvarchar(40) = NULL,
      @CityZipIndexID int = NULL,
      @StateID int = NULL,
      @ZipCode nvarchar(5) = NULL
      DECLARE @SQL nvarchar(1000)

      SET @SQL = 'SELECT E.ID, E.[Name]
      FROM dbo.tblEntityType AS ET
      RIGHT OUTER JOIN dbo.tblEntity AS E
      LEFT OUTER JOIN dbo.tblStates AS ST
      RIGHT OUTER JOIN dbo.tblCounties AS CO
      RIGHT OUTER JOIN dbo.tblCities AS CI
      RIGHT OUTER JOIN dbo.tblCityZipIndex AS CZI
      ON CI.CityFIPS = CZI.CityFIPS
      LEFT OUTER JOIN dbo.tblZipCodes AS ZC
      ON CZI.ZipCode = ZC.ZipCode
      ON CO.CountyFIPS = CI.CountyFIPS
      ON ST.StateFIPS = CO.StateFIPS
      ON E.CityZipIndexID = CZI.ID
      ON ET.ID = E.EntityTypeID
      WHERE ET.ID = ' + @EntityTypeID

      IF NOT @EntityName = NULL
                  SET @SQL = @SQL + ' AND E.[Name] = ' + char(34) + @EntityName + char(34)

      IF NOT @StreetNumber = NULL
                  SET @SQL = @SQL + ' AND E.StreetNumber = ' + char(34) + @StreetNumber + char(34)

      IF NOT @StreetName = NULL
                  SET @SQL = @SQL + ' AND E.StreetName = ' + char(34) + @StreetName + char(34)

      IF NOT @CityZipIndexID = NULL
                  SET @SQL = @SQL + ' AND CZI.ID = ' + @CityZipIndexID

      IF NOT @StateID = NULL
                  SET @SQL = @SQL + ' AND ST.ID = ' + @StateID

      IF NOT @ZipCode = NULL
                  SET @SQL = @SQL + ' AND ZC.ZipCode = ' + char(34) + @ZipCode + char(34)

      EXECUTE (@SQL)

All I ultimately want is string concatenation within a Stored Procedure, but I can't use any variables unless they are of the nvarchar type.  I can't simply convert these variables since they are numeric.  Any suggestions?
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
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 12 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