August 13, 2003 at 5:30 am
Hi there,
Please can you help. I'm getting nowhere trying to get this to work.
I have written a stored procedure as follows:
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'usp_count_grades_KS1')
DROP PROCEDURE usp_count_grades_KS1
GO
CREATE PROCEDURE usp_count_grades_KS1
@ui_category varchar(30)
AS
DECLARE year_cursor CURSOR
FOR SELECT distinct(DataInYear)
from akay.KS1
group by DataInYear
order by DataInYear
DECLARE @year int
OPEN year_cursor
FETCH NEXT FROM year_cursor into @year
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @year
EXEC ('select distinct(' + @ui_category + '), count(*)
from akay.KS1 where DataInYear = ' + @year + 'group by ' + @ui_category)
--EXEC ('select distinct(' + @ui_category + '), count(*)
--from akay.KS1 where DataInYear = ' + @year +
--'group by ' + @ui_category )
FETCH NEXT FROM year_cursor into @year
END
CLOSE year_cursor
DEALLOCATE year_cursor
GO
it creates the procedure ok but when i try to execute it, i get the following errors which i cannot seem to resolve...
1998
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Î'.
1999
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Ï'.
2000
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Ð'.
2001
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Ñ'.
2002
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Ò'.
anyone who can help is a star!!
Thanks
Meg
August 13, 2003 at 5:44 am
What parameter are you passing to the stored procedure in @ui_category? Is this a column name in your table? You may also want to put your select statement into a variable and then exec(variable).
Joseph
August 13, 2003 at 5:56 am
I guess you need to cast the Year to a character data type since it is int datatype and then concatenate it.
Relationships are like Banks.You Invest the Principal and then get the Interest.
He who knows others is learned but the wise one is one who knows himself.
August 13, 2003 at 6:56 am
Thanks for your help.
I changed the DECLARE @year int
to DECLARE @year varchar(4)
and its working now.
another question that i am having trouble with...my results are coming back like this..
KS1_REA_TS_AVPTS_AE_NAT 1998
----------------------- -----------
NULL 2
A 3271
A* 776
B 2749
C 3667
D 1399
E 3243
E* 1568
(8 row(s) affected)
KS1_REA_TS_AVPTS_AE_NAT 1999
----------------------- -----------
NULL 167
A 3375
A* 1103
B 2146
C 3312
D 2517
E 3121
E* 1334
(8 row(s) affected)
KS1_REA_TS_AVPTS_AE_NAT 2000
----------------------- -----------
NULL 68
A 3252
A* 923
B 2604
C 2921
D 2550
E 3105
E* 1274
(8 row(s) affected)
KS1_REA_TS_AVPTS_AE_NAT 2001
----------------------- -----------
NULL 198
A 3332
A* 1021
B 2469
C 3086
D 2202
E 3275
E* 1364
(8 row(s) affected)
KS1_REA_TS_AVPTS_AE_NAT 2002
----------------------- -----------
NULL 160
A 3217
A* 925
B 2612
C 3004
D 2457
E 3265
E* 1290
Do you think using a cursor it would ever be possible to kind of have something like this
KS1_REA_TS_AVPTS_AE_NAT 1998 1999 2000 2001
----------------------- ---- ---- ---- ----
A 10 15 201 300
B 78 852 124 55
etc....
??
thanks again
Meg
August 13, 2003 at 9:00 am
Well, you could modify your code to create a query to return the data like that:
CREATE PROCEDURE usp_count_grades_KS1
@ui_category varchar(30)
AS
DECLARE year_cursor CURSOR
FOR SELECT distinct(DataInYear)
from akay.KS1
group by DataInYear
order by DataInYear
DECLARE @year varchar(4)
Declare @Counts varchar(2000)
Set @Counts = 'CASE '
OPEN year_cursor
FETCH NEXT FROM year_cursor into @year
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @year
Set @Counts = @Counts + 'Sum(Case WHEN DataInYear = ' + @year + ' Then 1 Else 0 END) as Year' + @year + ','
FETCH NEXT FROM year_cursor into @year
END
CLOSE year_cursor
DEALLOCATE year_cursor
EXEC ('select ' + @ui_category + ', ' + Left(@Counts, Len(@Counts) - 1) + ' from akay.KS1 group by ' + @ui_category)
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply