Stored procedure with string as parameters not working -SQL

In my database 'Student_name' is set as varchar. And the stored procedure is:

 ALTER PROCEDURE [dbo].[SP_STUDENT]
 @STUDENT_NAME NVARCHAR(MAX),
    AS
     DECLARE
        @columns NVARCHAR(MAX) = '',
        @columnsname NVARCHAR(MAX) = '',
        @columnsnameA NVARCHAR(MAX) = '',

        @columnsB NVARCHAR(MAX) = '',
        @columnsnameB NVARCHAR(MAX) = '',

        @sql     NVARCHAR(MAX) = '';

         -- select the question num
         SET @columns = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) 
                    FROM submission1_details  WHERE Submission1_id=100

                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

        SET @columnsname = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub1_Q'+ cast(Question_no as varchar)
                    FROM submission1_details  WHERE Submission1_id=100

                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

                SET @columnsnameA = STUFF((SELECT distinct ','+' sub1_Q'+ cast(Question_no as varchar)
                    FROM submission1_details  WHERE Submission1_id=100

                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

                SET @columnsB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) 
                    FROM submission2_details  WHERE Submission2_id=500

                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

        SET @columnsnameB = STUFF((SELECT distinct ',' + QUOTENAME(cast(Question_no as varchar)) + ' sub2_Q'+ cast(Question_no as varchar)
                    FROM submission2_details  WHERE Submission2_id=500

                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') 
                ,1,1,'')

        print @columns

        SET @sql =N'
          select Submission_id,DRA_submission_id,SubmittedOn,Driver,VehicleType,VehicleNo,Interval, '+@columnsnameB +','+ @columnsnameA+ ' from (
         select Submission_id,DRA_submission_id,SubmittedOn,Driver ,VehicleType,'+@columnsname+',Question_no, Answer,VehicleNo,Interval from (
         select distinct t1.Submission_id,t1.DRA_submission_id,t1.Submitted_time AS SubmittedOn,t1.Driver_id as Driver, dvc_vehicle_types.vehicle_type_name AS VehicleType,dvc_vehicles.Vehicle_RegNo AS VehicleNo,
         dvc_checklist_intervals.Interval_description AS Interval,t2.Question_no t,t2.Answer A,t3.Question_no,t3.Answer from dvc_submission_header  t1 
         inner JOIN dvc_submission_details t2 ON  t1.Submission_id=  t2.Submission_id
          inner JOIN dvc_DRAsubmission_details  t3 ON  t1.DRA_submission_id=  
t3.Submission_id  INNER JOIN dvc_vehicles ON
t1.Vehicle_id=dvc_vehicles.Vehicle_id INNER JOIN  dvc_vehicle_types ON 
dvc_vehicles.Vehicle_type_id=dvc_vehicle_types.Vehicle_type_id  
INNER JOIN dvc_checklist_intervals ON t1.Interval_id = 
 dvc_checklist_intervals.Interval_id
WHERE t1.Student_name= '+ @STUDENT_NAME +' ';
  SET @sql +=N'
         ) as a
         PIVOT(
        MAX(A)
        for t  IN ('+ @columns +')
        ) AS pivot_table
        ) as b
        PIVOT(
        MAX(Answer)
        for Question_no  IN ('+ @columnsB +')
        ) AS pivot_table1;';

        print @sql

        -- execute the dynamic SQL
        EXECUTE sp_executesql @sql;

And I have executed the stored procedure as below:

EXEC [SP_STUDENT]  @STUDENT_NAME='Yuvan'

But it shows an error:

Conversion failed when converting the nvarchar value ‘Yuvan’ to data type int.

Please help to correct.

Answer

Do not concatenate string to build a statement. Use sp_executesql correctly and use parameters (if you really want to use a dynamic statement):

ALTER PROCEDURE [dbo].[SP_STUDENT]
   @STUDENT_id NVARCHAR(MAX),
   @STUDENT_name NVARCHAR(MAX),
   @EXAM_date NVARCHAR(MAX)
AS
BEGIN
   DECLARE 
      @sql nvarchar(max),
      @err int

   SET @sql = 
      N'SELECT * FROM student_details ' +
      N'WHERE student_id = @STUDENT_id AND student_name = @STUDENT_name AND exam_date = @EXAM_date'; 

   PRINT @sql
   EXECUTE @err = sp_executesql 
      @sql, 
      N'@STUDENT_id NVARCHAR(MAX), @STUDENT_name NVARCHAR(MAX), @EXAM_date NVARCHAR(MAX)',
      @STUDENT_id, @STUDENT_name, @EXAM_date

   RETURN (@err)   
END   

Notes:

• Define parameters data types carefully. Using nvarchar(max) is not needed. Use the appropriate data type, based on the columns data types.
• Pass datetime values using an unambiguous datetime format

If possibe, use simple SELECT statement:

ALTER PROCEDURE [dbo].[SP_STUDENT]
   @STUDENT_id NVARCHAR(MAX),
   @STUDENT_name NVARCHAR(MAX),
   @EXAM_date NVARCHAR(MAX)
AS
BEGIN
   SELECT * 
   FROM student_details
   WHERE 
      student_id = @STUDENT_id AND 
      student_name = @STUDENT_name AND 
      exam_date = @EXAM_date 
END