"Conversion Failed" error in SQL Server
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
AS
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
BEGIN
SET @SQL = @SQL + ' AND E.[Name] = ' + char(34) + @EntityName + char(34)
END
IF NOT @StreetNumber = NULL
BEGIN
SET @SQL = @SQL + ' AND E.StreetNumber = ' + char(34) + @StreetNumber + char(34)
END
IF NOT @StreetName = NULL
BEGIN
SET @SQL = @SQL + ' AND E.StreetName = ' + char(34) + @StreetName + char(34)
END
IF NOT @CityZipIndexID = NULL
BEGIN
SET @SQL = @SQL + ' AND CZI.ID = ' + @CityZipIndexID
END
IF NOT @StateID = NULL
BEGIN
SET @SQL = @SQL + ' AND ST.ID = ' + @StateID
END
IF NOT @ZipCode = NULL
BEGIN
SET @SQL = @SQL + ' AND ZC.ZipCode = ' + char(34) + @ZipCode + char(34)
END
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?
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
AS
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
BEGIN
SET @SQL = @SQL + ' AND E.[Name] = ' + char(34) + @EntityName + char(34)
END
IF NOT @StreetNumber = NULL
BEGIN
SET @SQL = @SQL + ' AND E.StreetNumber = ' + char(34) + @StreetNumber + char(34)
END
IF NOT @StreetName = NULL
BEGIN
SET @SQL = @SQL + ' AND E.StreetName = ' + char(34) + @StreetName + char(34)
END
IF NOT @CityZipIndexID = NULL
BEGIN
SET @SQL = @SQL + ' AND CZI.ID = ' + @CityZipIndexID
END
IF NOT @StateID = NULL
BEGIN
SET @SQL = @SQL + ' AND ST.ID = ' + @StateID
END
IF NOT @ZipCode = NULL
BEGIN
SET @SQL = @SQL + ' AND ZC.ZipCode = ' + char(34) + @ZipCode + char(34)
END
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?
ASKER
No, I cannot do temp tables because it will be a multi-user environment.
ASKER
Actually, I should ask this question...
If I create a Temp Table like this (because I do like the idea), if two users run the Stored Procedure at the same time, does it create these Temp Tables per session (so each user essentially has his or her own temp table) or will they conflict?
If I create a Temp Table like this (because I do like the idea), if two users run the Stored Procedure at the same time, does it create these Temp Tables per session (so each user essentially has his or her own temp table) or will they conflict?
my understanding is that they are per session/per user so it should work.
You should try it, but you could check the table qualifier -- instead of dbo.tempTableName look for gordon.tempTableName, etc. I know this works with non-temporary tables.
CyprexxDev,
>>No, I cannot do temp tables because it will be a multi-user environment.<<
I suggest you read up on the use of temporary tables in BOL.
>>No, I cannot do temp tables because it will be a multi-user environment.<<
I suggest you read up on the use of temporary tables in BOL.
But the bigger question is why in the world are you using Dynamic SQL for such a simple query, not to mention the use of double quotes (CHAR(34)). Let us know if you can use a solution without the overhead and risk of Dynamic SQL.
And finally the following is incorrect:
IF NOT @EntityName = NULL
It should be:
IF NOT @EntityName Is NULL
Remember the golden rule: NULL <> NULL
IF NOT @EntityName = NULL
It should be:
IF NOT @EntityName Is NULL
Remember the golden rule: NULL <> NULL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey acPerkins,
That is a great solution as far as the WHERE clause in concerned and I certainly want to use that over my original idea or Temp tables. The problem exists in the FROM clause somewhere. I'm getting the error "The multi-part identifier "CZI.ID" could not be bound". Any ideas on how to fix this? I get this error wuite often and after messing with the way I link tables in the where clause, I usually fix it - that's why my WHERE clause was originally so messy. I stuck it in a view and read how SQL server wanted to build it and used that. Thanks!
That is a great solution as far as the WHERE clause in concerned and I certainly want to use that over my original idea or Temp tables. The problem exists in the FROM clause somewhere. I'm getting the error "The multi-part identifier "CZI.ID" could not be bound". Any ideas on how to fix this? I get this error wuite often and after messing with the way I link tables in the where clause, I usually fix it - that's why my WHERE clause was originally so messy. I stuck it in a view and read how SQL server wanted to build it and used that. Thanks!
ASKER
I figured it out... The FROM clause has to be:
FROM dbo.tblEntityType AS ET
RIGHT OUTER JOIN dbo.tblEntity AS E ON ET.ID = E.EntityTypeID
LEFT OUTER JOIN dbo.tblStates AS ST
RIGHT OUTER JOIN dbo.tblCounties AS CO ON ST.StateFIPS = CO.StateFIPS
RIGHT OUTER JOIN dbo.tblCities AS CI ON CO.CountyFIPS = CI.CountyFIPS
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 E.CityZipIndexID = CZI.ID
SQL Server is very touchy about this - Access handles this much better. Just what I'm used to :-) Thanks for your help - saved a lot of unnecessary code.
FROM dbo.tblEntityType AS ET
RIGHT OUTER JOIN dbo.tblEntity AS E ON ET.ID = E.EntityTypeID
LEFT OUTER JOIN dbo.tblStates AS ST
RIGHT OUTER JOIN dbo.tblCounties AS CO ON ST.StateFIPS = CO.StateFIPS
RIGHT OUTER JOIN dbo.tblCities AS CI ON CO.CountyFIPS = CI.CountyFIPS
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 E.CityZipIndexID = CZI.ID
SQL Server is very touchy about this - Access handles this much better. Just what I'm used to :-) Thanks for your help - saved a lot of unnecessary code.
The join syntax in the MS Access SQL dialect is different to T-SQL.
Your FROM clause is missing a condition for:
LEFT OUTER JOIN dbo.tblStates AS ST
But if it works for you, that is fine with me.
Your FROM clause is missing a condition for:
LEFT OUTER JOIN dbo.tblStates AS ST
But if it works for you, that is fine with me.
into #YourTempTable
From AllYourJoined Tables
IF NOT @EntityName = NULL
BEGIN
DELETE FROM #YourTempTable WHERE E.[Name] <> @EntityName
END
IF Etc....
select * from #YourTempTable
will that do it for you?