N”’ prefix is added by default while executing Stored Procedure

While executing stored procedure, I’m passing 4 arguments. After passing the arguments through GUI mode, I’m seeing that some prefixes has been added by the sql itself.

USE [du_Attendance]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[GET_ATTENDANCE_REPORT_FOR_FACULTY]
        @startdate = '1/9/2018',
        @enddate = '1/12/2018',
        @coursecode = N'''BSCCS''',
        @subjectcode = N'''CSHT101'''

SELECT  @return_value as 'Return Value'

GO

So when I am removing the N”, it’s giving me an error saying Invalid column name ‘BSCCS’. What does it mean ?

Full Stored Procedure –

CREATE PROCEDURE GET_ATTENDANCE_REPORT_FOR_FACULTY
@startdate DATE,  
@enddate DATE,
@coursecode varchar(10),
@subjectcode varchar(10)


AS BEGIN

DECLARE @cols as varchar(2000);
DECLARE @cols_select as varchar(MAX);
declare @col varchar(20)
declare @cols_copy varchar(100)
DECLARE @query as varchar(MAX);

WITH cte (startdate)
AS 
(SELECT
        @startdate AS startdate
    UNION ALL
    SELECT
        DATEADD(DD, 1, startdate) AS startdate
    FROM cte
    WHERE startdate < @enddate
)
select c.startdate
into #tempDates
from cte c
where datename(weekday, c.startdate) <> 'Sunday';

SELECT
    @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(CONVERT(CHAR(6),startdate, 106))
        FROM #tempDates
        FOR XML PATH (''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

select @cols_copy = replace(@cols, ',', '')

select @cols_select = SUBSTRING(@cols_select, 2, len(@cols_select) - 1)


SET @query = 'SELECT RollNo,FirstName,LastName, ' + dbo.fn_convert_cols(@cols) + ' from 
             (
                select S.RollNo,U.FirstName,U.LastName,
                D.startdate,
                convert(CHAR(6), startdate, 106) PivotDate
                from #tempDates D,Attendance A, Student S, UserDetails U
                where convert(CHAR(6), D.startdate, 106) = convert(CHAR(6), A.Date, 106) and A.EnrollmentNo=S.EnrollmentNo and A.EnrollmentNo=U.userID and A.CourseCode=' + @coursecode + ' and A.SubjectCode =' + @subjectcode + '
            ) x
           pivot 
           (
                count(startdate)
                for PivotDate in (' + @cols + ')
           ) p '

EXECUTE (@query)
drop table #tempDates
END

The problem with this is I am passing the parameters from C# code so only ‘BSCCS’ is passed into the stored procedure resulting in the above mentioned error.

C# Code –

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(Constants.getAttendanceForFaculty, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@startdate", startdate);
cmd.Parameters.AddWithValue("@enddate", enddate);
cmd.Parameters.AddWithValue("@coursecode", courseCode);
cmd.Parameters.AddWithValue("@subjectcode", subCode);
DataTable dt = new DataTable();
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();

Answer

you should change your dynamic query like this, otherwise – because of dynamic query – SQL executes your parameters like columns.

I changed

A.CourseCode=' + @coursecode + ' and A.SubjectCode =' + @subjectcode + '

to this

A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode + '''

SET @query = 'SELECT RollNo,FirstName,LastName, ' + dbo.fn_convert_cols(@cols) + ' from 
             (
                select S.RollNo,U.FirstName,U.LastName,
                D.startdate,
                convert(CHAR(6), startdate, 106) PivotDate
                from #tempDates D,Attendance A, Student S, UserDetails U
                where convert(CHAR(6), D.startdate, 106) = convert(CHAR(6), A.Date, 106) and A.EnrollmentNo=S.EnrollmentNo and A.EnrollmentNo=U.userID and A.CourseCode=''' + @coursecode + ''' and A.SubjectCode =''' + @subjectcode + '''
            ) x
           pivot 
           (
                count(startdate)
                for PivotDate in (' + @cols + ')
           ) p '