Dynamic Sql Query Not Executed?

Question

I have below dynamic query:

SET @DynamicPIVOT = 'SELECT * FROM (
    SELECT StudentId, IsForeNoon,Date as date1 FROM Attendance_FN_AN
) as  Books
PIVOT (
    Count(IsForeNoon) FOR date1 IN (' + @cols + ')
    ) as  Result'
END

exec @DynamicPIVOT;

When I execute the above dynamic query, single quotation not removed from the front and last so that The Issue come for

The name 'SELECT * FROM (
    SELECT StudentId, IsForeNoon,Date as date1 FROM Attendance_FN_AN
) as  Books
PIVOT (
    Count(IsForeNoon) FOR date1 IN ([2015-04-16])
    ) as  Result' is not a valid identifier.

Any solution for this?

HaveNoDisplayName · Accepted Answer · 2015-04-29 18:19:38Z

1

Instead of exec @DynamicPIVOT; use exec(@DynamicPIVOT).

Better to use SP_EXECUTESQL. The big thing about SP_EXECUTESQL is that it allows you to create parameterized queries which is very good if you care about SQL injection.

answered Apr 29, 2015 at 18:19

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Sql Query Not Executed?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related