Conversion problem

  • Hi,

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

    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.

    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

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

  • 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

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

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

    - 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply