Error converting nvarchar - but there is no nvarchar

[Imakeoil]

Hello,

I am trying to call one sp with another but am getting an 'Error converting nvarchar to real' error. However, there is no nvarchar in either report. Not only that but other reports that call the sp work fine and seem to be set up the same.
I am running this command

DECLARE @startd int, @endd int

EXECUTE xxx.dbo.[mrm_scheduled_report_date_procedure] @startd, @endd,OUTPUT

And it calls this sp:

ALTER PROCEDURE [dbo].[scheduled_report_date_procedure]
(@endshift real, @spanshift real, @startindex real OUTPUT, @endindex real OUTPUT)

AS

/**************************************************************************************************************************************************
General Procedure Used to Set Dates for scheduled reports

@endshift = set the report end shift with an integer - number of shifts back from most recent completed shift at query runtime.
FLAGS for @endshift: -1 = Current Shift
-2 = Week to Date (Monday Dayshift)
-3 = Month to Date
-4 = Year to Date

@spanshift = set the start date with an integer - number of previous shift to span from final endshift

***************************************************************************************************************************************************/
/*
-- Test code
DECLARE @endshift real, @spanshift real
SET @endshift = 0
SET @spanshift = 0
-- End Test Code
*/

DECLARE @eDate smalldatetime, @eTemp smalldatetime, @shiftindexstart int, @shiftindexend int

SET @eTemp = CURRENT_TIMESTAMP -- Today's date
SET @eDate = @eTemp
- DATEPART(hh, @eTemp)/24.0
- DATEPART(mi, @eTemp)/24.0/60.0
- DATEPART(s, @eTemp)/24.0/3600.0
- DATEPART(ms, @eTemp)/24.0/3600.0/1000.0

IF (DATEPART(hh, @eTemp) >= 7 AND DATEPART(hh, @eTemp) < 18) OR (DATEPART(hh, @eTemp) = 6 AND DATEPART(mi, @eTemp) >= 30) OR (DATEPART(hh, @eTemp) = 18 AND DATEPART(mi, @eTemp) <= 30) -- IF Query is Executed during Dayshift
BEGIN
IF @endshift >= 0
BEGIN
SET @shiftindexend = (SELECT shiftindex FROM exproot
WHERE shiftdate = @eDate AND shift# = '1') - 1 - @endshift
END
ELSE
BEGIN
SET @shiftindexend = (SELECT shiftindex FROM exproot
WHERE shiftdate = @eDate AND shift# = '1') - 1
END
END
ELSE
BEGIN
IF @endshift >= 0
BEGIN
SET @shiftindexend = (SELECT shiftindex FROM exproot
WHERE shiftdate = @eDate AND shift# = '2') - 1 - @endshift
END
ELSE
BEGIN
SET @shiftindexend = (SELECT shiftindex FROM exproot
WHERE shiftdate = @eDate AND shift# = '2') - 1
END
END

IF @endshift = -1.0 -- Current shift
BEGIN
SET @shiftindexend = (SELECT MAX(shiftindex) FROM exproot)
SET @shiftindexstart = @shiftindexend - @spanshift
END

IF @endshift= -2.0 -- WTD
BEGIN
DECLARE @week real
SET @week = (SELECT finweek FROM exproot WHERE shiftindex = @shiftindexend)
SET @shiftindexstart = (SELECT MIN(shiftindex) FROM exproot WHERE finweek = @week
AND DATEPART(yy, @eDate) = DATEPART(yy, shiftdate)) + 4 -- '+ 4' Week Starts on Monday Dayshift
END

IF @endshift = -3.0 -- MTD
BEGIN
SET @shiftindexstart = (SELECT MIN(shiftindex) FROM exproot WHERE DATEPART(mm, @eDate) = DATEPART(mm, shiftdate)
AND DATEPART(yy, @eDate) = DATEPART(yy, shiftdate))
END

IF @endshift = -4.0 -- YTD
BEGIN
SET @shiftindexstart = (SELECT MIN(shiftindex) FROM exproot WHERE DATEPART(yy, @eDate) = DATEPART(yy, shiftdate))
END



IF @endshift >= 0 -- @spanshift = recurse #shifts from end date
BEGIN
SET @shiftindexstart = @shiftindexend - @spanshift
END

-- Don't comment out in Procedure
SELECT @startindex = @shiftindexstart
SELECT @endindex = @shiftindexend


Can anyone shed some light on this?

Thanks in Advance

 

[SQLBill]

When you get the error, you should be able to double-click on it and have it highlight the spot of the error in the code.

Also, check the columns shiftindex and finweek. Are either of them NVARCHAR?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLSister]

Usually that is a data issue, one of the fields in the results set contains a value that can't convert. This one reason why you don't store things like dates or number in varchar or nvarchar fields because bad data gets into them.

And why are you using real datatype when the comments clearly indicate they should be integrers. Real is not an exact datatype and is a poor choice for pretty much anything.

"NOTHING is more important in a database than integrity." ESquared