October 11, 2010 at 4:35 am
Hi ALL,
I am getting error in Stored procedure when passing TotalEmployee
Please check words in Bold.Also i am getting result as percentage value without percentage symbol.Where to percentage symbol.(for example i am getting 75 i should get 75 %)
Error is Must declare the scalar variable "@TotalEmployee".
alter PROCEDURE GetSchedule @date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID INT AS
DECLARE @query VARCHAR(MAX),@TotalEmployee int
BEGIN
set @TotalEmployee=4
SET @query = 'SELECT ' + CHAR(39) + 'b/w 24 and 40' + CHAR(39) + ' AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(varchar,dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours<40.00 Then EmpNo end),@TotalEmployee)*100) as empno
FROM TestTable4 group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable
UNION
SELECT ' + CHAR(39) + 'Greater Than 40' + CHAR(39) + ' AS TotalHours, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
convert(varchar,dbo.FDiv(Count(Case When Totalhours>=40.00 Then EmpNo end),@TotalEmployee)*100) as empno
FROM TestTable4 group by WeekStartDate) AS SourceTable
PIVOT
(
max(empno)
FOR WeekStartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable';
EXEC(@query);
END
October 11, 2010 at 10:44 pm
In this case, you must use a system store procedure sp_executesql because your dynamic query is containing a parameter.
EXEC sp_executesql @query,N'@TotalEmployee',@TotalEmployee
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply