SPROC GROUP BY variables

[icarus5000]

Hi,

I'm trying to create a sproc that is using parameters for dynamic field names. The following sql statement is causing problems with using the sproc parameters in the GROUP BY clause. Is there a way around this apart from writing an IF statement for each field iteration.

SELECT @group1,@group2,CRS_ID,COUNT(STATUS) as status_total
FROM TBLMAIN
WHERE STATUS='C' AND COMP_ID=@CompID
GROUP BY @group1, @group2, CRS_ID

Thanks,
Ic

 

[r937]

The following sql statement is causing problems

do you mind sharing what those problems might be?



r937.com | rudy.ca

 

[icarus5000]

Whoops! Sorry.

Here's the error from query analyser

Server: Msg 164, Level 15, State 1, Procedure sp_assigned_summary, Line 30
GROUP BY expressions must refer to column names that appear in the select list.

Thanks,
Ic

 

[Jamfool]

you will need to use dynamic sql if you want to do it this way.

DECLARE @group1 as varchar(300)
DECLARE @group2 as varchar(300)
DECLARE @CompID as varchar(300)
select @CompID = 'c1'
select @group1 = 'g1'
select @group2 = 'g2'

DECLARE @sql as varchar(8000)

select @sql = 'SELECT ' + @group1 + ',' + @group2 + ',CRS_ID,COUNT(STATUS) as status_total
FROM TBLMAIN
WHERE STATUS=''C'' AND COMP_ID=' + @CompID +
' GROUP BY ' + @group1 + ', ' + @group2 + ', CRS_ID'

print @sql
exec (@sql)

 

[vongrunt]

SELECT @group1, @group2, CRS_ID, status_total
FROM
(	SELECT CRS_ID, COUNT(STATUS) as status_total
	FROM TBLMAIN
	WHERE STATUS='C' AND COMP_ID=@CompID
	GROUP BY CRS_ID
) blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

Damn... I'm dunkin' too much.

Simply name columns:

SELECT @group1 as blah,@group2 as blah2 ,CRS_ID,COUNT(STATUS) as status_total
FROM TBLMAIN
WHERE STATUS='C' AND COMP_ID=@CompID
GROUP BY blah1, blah2, CRS_ID

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[icarus5000]

This worked out very nicely.

Thanks a million!
-Ic