what is the error in the stored procedure

Question

4.00/5 (1 vote)

See more:

Hi friends,
This is my query

SQL

alter procedure sp_getmarkforclass(@classid int,@examid int)
as
begin
declare @paramlist varchar(max),@query nvarchar(max)
set @paramlist=STUFF((select distinct ',[' + SubjectId + ']' from School.dbo.Mark_details for xml path('')),1,1,'')
--print @paramlist
set @query='select' + @paramlist + 'from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in (' + @paramlist + ')) pvt'
exec @query
end

MarkId	ExamId	Subid  admnumberMark	Mm	Grade	classid
66	6	23011	103	77	100	NULL	12
67	6	23012	103	88	100	NULL	12
68	6	23013	103	77	100	NULL	12
69	6	23014	103	77	100	NULL	12
70	6	23015	103	66	100	NULL	12
71	6	23011	102	77	100	NULL	12
72	6	23012	102	88	100	NULL	12
73	6	23013	102	99	100	NULL	12
74	6	23014	102	99	100	NULL	12
75	6	23015	102	77	100	NULL	12
76	6	23011	100	77	100	NULL	12
77	6	23012	100	77	100	NULL	12
78	6	23013	100	88	100	NULL	12
79	6	23014	100	99	100	NULL	12
80	6	23015	100	88	100	NULL	12

but the error is

The name 'select[23011],[23012],[23013],[23014],[23015]from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in ([23011],[23012],[23013],[23014],[23015])) pvt' is not a valid identifier.

i want result as'

studentname rollnumber tamil(23011) english maths science socialscience
XXXXXX 102 77 88 99 99 77

like above for class and exam wise.here i just change markobtained as mm but same column name used to the query

Posted 27-Dec-12 2:18am

baskaran chellasamy

Updated 27-Dec-12 2:20am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

M.Edmison · Answer 1 · 2012-12-27T02:39:00

To fix that I would probably run the first query to see what answer you get there and adjust to the needed result, then you can correct the dynamic portion which is failing.

Which would be:

SELECT DISTINCT subjectId from
(
    SELECT * From school.dbo.Mark_details
    SUM(MarkObtained)
)

which may require some adjusting but that can direct where the values are coming from, then adjust your dynamic and pivot from there.

If you want to run that as a script and trouble shoot from there you can use the following:

--alter procedure sp_getmarkforclass(@classid int,@examid int)
--as
--begin
declare @paramlist varchar(max),@query nvarchar(max)
set @paramlist=STUFF((select distinct ',[' + SubjectId + ']' from School.dbo.Mark_details for xml path('')),1,1,'')
--print @paramlist
set @query='select' + @paramlist + 'from (select * from Mark_details)src pivot(sum(MarkObtained) for SubjectId in (' + @paramlist + ')) pvt'
exec @query
--end

And can run it as a script to trouble shoot that SP.

Hope that helps