Sql-server – Conversion error on paramater but not variable

sql serverstored-procedurestype conversion

I've got a stored procedure that accepts a parameter;

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)

I call this stored procedure and pass in a uniqueidentifier, something like this;

EXEC [Stored_Proc_Name] 'a6ed99c1-29c8-43f4-9e3a-0065e6dc7fc1'

The stored procedure does a bit of XML processing and returns a result set, it's fairly simple.

For a few paramaters that are passed I'm getting a type conversion error;

Msg 8114, Level 16, State 5, Procedure Stored_Proc_Name, Line 0
Error converting data type nvarchar to int.

I initially thought that this was an issue with the data but the issue resolves itself if i declare a local variable within the stored procedure, like this and then use that local variable in place of the parameter. The only places that this parameter is used is within WHERE clauses within my stored proc.

CREATE PROCEDURE [dbo].[Stored_Proc_Name] (@ParamID NVARCHAR(255) = NULL)
AS
DECLARE @localID nvarchar(255)
SET @localID = @ParamID

The issue is resolved and the procedure runs fine.

Any ideas on the next step of the investigation or something obvious that I've missed?

Update:

Upon further investigation, this was an issue with an XML attribute missing a [1] so it appears to have been trying to convert multiple attributes which was causing the error. I'm currently investigating how it was fixed with the variable and will update once I have a sensible answer. I'm going to mark the answer as accepted so that somebody gets the bounty.

Best Answer

I'm wondering if the parameter/variable situation is really just masking an unrelated implicit conversion within your query. This is a stretch, and someone who's better with the internals of SQL Server may be able to clarify if I'm completely wrong here, but I suspect you are getting different execution plans when switching between the parameter and variable approach. The differences in the plans may be drastic enough where an implicit varchar → int conversion error is only occurring with the parameterized approach based on join order as opposed to the variable approach as the variable isn't being sniffed properly and an entirely different join order is being used as a result.

To test my theory, throw an OPTION (RECOMPILE) statement at the end of your query when you convert your stored procedure to use a variable and see what happens. If the error occurs again, you are probably running into a different implicit conversion within your query that only occurs with a particular execution plan, meaning the error you're seeing isn't related to the input, but rather data already in the system and how you're joining tables within your statement.

Related Solutions

Sql-server – ‘Conversion failed’ error with ORDER BY CASE expression

Your CASE expression can only have one datatype. But yours is mixing datatypes and the highest one is being used (as per datatype precedence). So nvarchar is changed to int based on these rules.

You need a case per datatype or per column

Example:

CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'JobNumber' THEN J.JobNumber
                  WHEN @OrderBy = 'CustID' THEN J.CustID
                  WHEN @OrderBy = 'DateIn' THEN J.DateIn
                  WHEN @OrderBy = 'DateDue' THEN J.DateDue
                  WHEN @OrderBy = 'DateOut' THEN J.DateOut
                  WHEN @OrderBy = 'Status' THEN J.MasterJobStatusID 
                  ELSE NULL
                END
            END DESC,
CASE WHEN @OrderDirection = 'DESC' THEN
                 CASE 
                  WHEN @OrderBy = 'OrderNumber' THEN J.OrderNumber
                  ELSE NULL
                END
            END DESC

Sql-server – Declaring a scalar variable that its a table valued parameter

The problem is here:

WHERE   (tsl.TransmittalSiteLeadFacility = @facilities

You are trying to compare a column on the left to a table on the right. This is like saying:

FROM sys.objects
WHERE object_id = sys.columns

...which obviously makes no sense.

That where clause doesn't belong there at all. You've already ensured that the facilities listed in the incoming table-valued parameter are matched to TransmittalSheet by using it in the JOIN clause. There is no need to filter on that match again.

My guess after several rounds of teeth-pulling is you want this:

CREATE PROCEDURE dbo.GetImpDocsThree
  @facilities dbo.Facilities ReadOnly,
  @facilitydivisionID tinyint = 6
AS
BEGIN
  SET NOCOUNT ON; 

  ;WITH f AS
  (
    SELECT FacilityID FROM @faclities
    UNION ALL SELECT @facilitydivisionID
  ) 
  SELECT  di.DocumentNumber, di.DocumentVersion, di.DocumentTitle, 
    ts.TransmittalNumber, ts.TransmittalVersion,
    FacilityImpDate = COALESCE(ts.TransmittalSiteLeadImpDate, ts.TransmittalImpDate)
   FROM dbo.DocumentInfo AS di
   INNER JOIN dbo.TransmittalSheet AS ts 
     ON di.DocumentTransmittalImp = ts.TransmittalID
   INNER JOIN f 
     ON tsl.TransmittalSiteLeadFacility = f.FacilityID;
END
GO

Or just insert 6 into the TVP up front too, and ditch the additional single facility variable altogether.

Best Answer

Related Solutions

Sql-server – ‘Conversion failed’ error with ORDER BY CASE expression

Sql-server – Declaring a scalar variable that its a table valued parameter

Related Question