Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Conversion problem Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 14, 2010 2:43 AM
Points: 4, Visits: 72
Hi,

I have a SP to return a datetime value. However, when I EXECUTE the SP I always get this error

[color=#FF0000]Msg 257, Level 16, State 3, Procedure sp_date_after_clarify_close, Line 57
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.[/color]

Here is the complete code.

Thanks!


----------------------------------------------------------------
Main Program


DECLARE @date_after_clarify_close datetime

EXEC @date_after_clarify_close=dbo.sp_date_after_clarify_close
@date_clarify_close,
@int_issue_id,
''


-----------------------------------------------------------------

STORED PROCEDURE


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_date_after_clarify_close]
@date_clarify_close datetime,
@int_issue_id int,
@date_after_clarify_close datetime OUTPUT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @date_temp datetime

DECLARE cr_date_after_clarify_close CURSOR
FOR
SELECT date_response
FROM responses
WHERE issue_id = @int_issue_id
ORDER BY date_response

OPEN cr_date_after_clarify_close

FETCH NEXT FROM cr_date_after_clarify_close into @date_temp

WHILE @@FETCH_STATUS = 0
BEGIN

IF @date_temp = @date_clarify_close
BEGIN
FETCH NEXT FROM cr_date_after_clarify_close into @date_temp

SET @date_after_clarify_close = @date_temp
END
ELSE
FETCH NEXT FROM cr_date_after_clarify_close into @date_temp

END

CLOSE cr_date_after_clarify_close

DEALLOCATE cr_date_after_clarify_close

RETURN (@date_after_clarify_close) --> If I remark this RETURN then it's working fine.

END

--------------------------------------------------------------------
Post #526305
Posted Tuesday, July 1, 2008 1:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:40 AM
Points: 7,001, Visits: 8,439
Check RETURN in bol !
Return is not used to fillup output variables, but it gives an integer value to the calling object. (mostly used to indicate OK or not OK)

To fill up the output variable, just use a set or select statement !

in your case, just use RETURN without anything else at that place


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #526353
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse