October 18, 2011 at 11:50 pm
Hi All,
I am not that expert in writing sql queries and stored procedures. I am trying to write a stored procedure and I am getting the following errors when I am trying to create the following stored procedure. What I suspect is use of a sql parameter in the group by clause. If so how can I make this stored procedure work?
CREATE PROCEDURE spReport
@rLevel nvarchar(50),
@aSelected nvarchar(64),
@wType nvarchar(20),
@dSelected nvarchar(20),
@StartDate smalldatetime,
@EndDate smalldatetime
AS
BEGIN
SET NOCOUNT ON;
declare @total float
if (@dSelected = null)
begin
select @total = sum(total) from bs where @rLevel = CHAR(39)+ @aSelected+CHAR(39)
and wtype = '' + @wType + '' and bdate between CHAR(39)+@StartDate+CHAR(39) and CHAR(39)+@EndDate+CHAR(39)
select @rLevel, bdays, str(sum(total*100/@total ),38,2) as percentages, sum(total)as total1
from bs
where @rLevel= CHAR(39)+@aSelected+CHAR(39) and wtype = CHAR(39)+@wType+CHAR(39) and
bdate between CHAR(39)+@StartDate+CHAR(39) and CHAR(39)+@EndDate+CHAR(39)
group by @rLevel, bdays
order by
CASE @rLevel
WHEN 'r' THEN r
WHEN 'D' THEN D
WHEN 'S' THEN S
END
,bdays
end
END
Error: Each GROUP BY expression must contain at least one column that is not an outer reference.
I did not understand what I did wrong.
Other than this I am having problem in using the single quotes, Is the way I am using it is it correct?
October 19, 2011 at 3:31 am
select @rLevel, bdays, str(sum(total*100/@total ),38,2) as percentages, sum(total)as total1
from bs
where @rLevel= CHAR(39)+@aSelected+CHAR(39) and wtype = CHAR(39)+@wType+CHAR(39) and
bdate between CHAR(39)+@StartDate+CHAR(39) and CHAR(39)+@EndDate+CHAR(39)
group by @rLevel, bdays
@rLevel is not required in group by clause. Remove it.
October 19, 2011 at 5:32 am
This was removed by the editor as SPAM
October 23, 2011 at 6:46 pm
Thank you both of your for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply