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?
Upon further investigation, this was an issue with an XML attribute missing a  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.