Conversion error on paramater but not variable

Question

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.

Answer 1

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.