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

Advertisement

Answer