getting erro in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting erro in stored procedure

Author

Topic

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 12:48:11

Hi,

Getting error when i compile below stored procedure.Please help me to solve this issue.

Please run this table and stored procedure.

CREATE TABLE SAR_WeekData(
empno int,
TotalHours numeric(10,2),
atnDate datetime,
)

SET IDENTITY_INSERT mytable ON

Insert into SAR_WeekData(empno,TotalHours,atnDate)
select '101','45.00','8/1/2010' union all
select '101','44.50','8/8/2010' union all
select '101','38.60','8/15/2010' union all

select '102','38.00','8/1/2010' union all
select '102','46.00','8/8/2010' union all
select '102','38.00','8/15/2010' union all

select '103','56.00','8/1/2010' union all
select '103','62.00','8/8/2010' union all
select '103','44.00','8/15/2010' union all

select '104','46.00','8/1/2010' union all
select '104','32.00','8/8/2010' union all
select '104','35.00','8/15/2010'

exec SAR_Sp_GetSchedule '8/1/2010','8/8/2010','8/15/2010','8/22/2010',1

ALTER PROCEDURE [dbo].[SAR_Sp_GetSchedule] (@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignID INT,@TotalEmployee int)
AS
DECLARE @query VARCHAR(MAX)
BEGIN
SET @query = 'SELECT '+ CHAR(39) + '24 and 40' + CHAR(39) + ' AS ScheduledHrs, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
'FROM
(SELECT WeekStartDate,
CONVERT(VARCHAR,convert(numeric (4,0),dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours<=40.00 Then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)) + '%' as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' 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,convert(numeric (4,0),dbo.FDiv(Count(Case When Totalhours>40.00 Then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)) + '%' as empno
FROM
SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' 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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-03 : 12:57:07

What's the exact error message you're getting?

naresh0407
Starting Member

30 Posts

Posted - 2010-11-03 : 13:00:18

This is my error message.

Msg 402, Level 16, State 1, Procedure SAR_Sp_GetSchedule, Line 5
The data types varchar and varchar are incompatible in the modulo operator.
Error is in this query

(SELECT WeekStartDate,
CONVERT(VARCHAR,convert(numeric (4,0),dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours<=40.00 Then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)) + '%' as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-03 : 13:57:41

Instead of EXEC() do a print on @query.
The output then will show what's wrong with the statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-11-03 : 14:14:45

quote:
Originally posted by naresh0407

This is my error message.

Msg 402, Level 16, State 1, Procedure SAR_Sp_GetSchedule, Line 5
The data types varchar and varchar are incompatible in the modulo operator.
Error is in this query

(SELECT WeekStartDate,
CONVERT(VARCHAR,convert(numeric (4,0),dbo.FDiv(Count(Case When Totalhours>=24.00 and TotalHours<=40.00 Then EmpNo end),'+cast(@TotalEmployee as varchar)+')*100)) + '%' as empno
FROM SAR_WeekData where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ' group by WeekStartDate) AS SourceTable

Why are you casting it to varchar?
Firt have all the calculations done and then cast it to varchar.

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-03 : 16:48:26

Thank you for the DDL. Now we need to correct it and get it into ISO-11179 data element names, add constraints, etc.

You need to use the ISO-8601 date formats and learn the ANSI INSERT INTO syntax. It would also help if you know to write readable SQL with proper idioms (i.e. "work_hrs_tot >= 24.00 AND work_hrs_tot <= 40.00" becomes ""work_hrs_tot BETWEEN 24.00 AND 40.00"). First, clean up the table a little bit (it needs more constraints, etc. but just the basics for now):

CREATE TABLE SAR_WeekData
(emp_nbr INTEGER NOT NULL,
atn_date DATE NOT NULL,
work_hrs_tot DECIMAL (10,2) NOT NULL
CHECK(work_hrs >= 0.00),
PRIMARY KEY (emp_nbr, atn_date)); -- must have a key to be a table!

We would NEVER use IDENTITY in a properly designed schema. And we never use SQL to format reports and display headers -- that means your CONVERT() is out. This is a completely wrong way to use the language.

INSERT INTO SAR_WeekData(emp_nbr, work_hrs, atn_date)
VALUES ('101', 45.00, '2010-08-01'), -- iso-8601 dates, not dialect
('101', 44.50, '2010-08-08'), -- why were deciamls in quotes?
('101', 38.60, '2010-08-15'),
('102', 38.00, '2010-08-01'),
('102', 46.00, '2010-08-08'),
('102', 38.00, '2010-08-15'),
('103', 56.00, '2010-08-01'),
('103', 62.00, '2010-08-08'),
('103', 44.00, '2010-08-15'),
('104', 46.00, '2010-08-01'),
('104', 32.00, '2010-08-08'),
('104', 35.00, '2010-08-15');

A good SQL programmer will create a reporting periods table that is shared for all reports. He will not try to build something on the fly with CONVERT() function calls. SQL is a declarative data language and you write it like 1950's COBOL procedural code. This is like driving nails with a sandwich instead of a hammer.

We also do not use proprietary things like PIVOT unless we absolutely have to. We do not write UDFs unless we absolutely have to.

Weeks have an ISO numbering (yyyy-www) and month have a MySQL month name (yyyy-mm-00) that you can use.

CREATE TABLE WeeklyReportPeriods
(report_period_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (report_period_name LIKE '[12][0-9][0-9][0-9]-[0-9][1-9]'
OR report_period_name LIKE '[12][0-9][0-9][0-9]-[01][1-9]-00'),
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date < period_end_date)),
etc);

Now we can declare a VIEW and use it instead of procedural code:

CREATE VIEW EmpHourlyReport (emp_nbr, report_period_name, work_hrs_tot)
AS
SELECT SAR.emp_nbr, WP.report_period_name, SUM(SAR.work_hrs)
FROM SAR_WeekData AS SAR
LEFT OUTER JOIN
WeeklyReportPeriods AS WP
ON SAR.atn_date
BETWEEN WP.period_start_date AND WP.period_end_date
GROUP BY SAR.emp_nbr, WP.report_period_name;

What you are trying to do is hidden in all that messy code. I think it is a report, complete with formatting, on the total work hours by employee, classified by a category system. So let's see if this will give you a clue as to how to write proper declarative SQL.

SELECT emp_nbr,
SUM (CASE WHEN work_hrs_tot < 24.00
THEN work_hrs_tot ELSE 0.00 END) AS part_time,
SUM (CASE WHEN work_hrs_tot BETWEEN 24.00 AND 40.00
THEN work_hrs_tot ELSE 0.00 END) As regular,
SUM (CASE WHEN work_hrs_tot > 40.00
THEN work_hrs_tot ELSE 0.00 END) AS overworked
FROM EmpHourlyReport
WHERE report_period_name
IN (@in_date1, @in_date2, @in_date3, @in_date4)
AND campaign_id = @in_campaign_id
GROUP BY emp_nbr;

This should run 1-2 orders of magnitude faster than what you are writing now. And it is portable. And it is readable so it can be maintained.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-03 : 17:17:18

Enclose your formatted code with code tags: [code] and [/code]

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-04 : 07:36:40

<<
'2010-08-01'), -- iso-8601 dates, not dialect
>>

In SQL Server, it is correct format for DATE column. But for DATETIME column the only two unambigious formats are

YYYYMMDD (with or without time)
YYYY-MM-DDTHH:MM:SS (Hyphenated format with Time seperator T)
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources